<!DOCTYPE html>
<html lang="en-US">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>1.1  今日目标 | AJ</title>
    <meta name="generator" content="VuePress 1.5.0">
    <link rel="shortcut icon" href="/favicon.ico" type="image/x-icon">
    <script>
            var _hmt = _hmt || [];
            (function() {
            var hm = document.createElement("script");
            hm.src = "https://hm.baidu.com/hm.js?51b8c2e72d1adf96524638ce85bb7d72";
            var s = document.getElementsByTagName("script")[0]; 
            s.parentNode.insertBefore(hm, s);

            // 引入谷歌,不需要可删除这段
            var hm1 = document.createElement("script");
            hm1.src = "https://www.googletagmanager.com/gtag/js?id=G-B7351EYS04";
            var s1 = document.getElementsByTagName("script")[0]; 
            s1.parentNode.insertBefore(hm1, s1);
            })();

            // 谷歌加载,不需要可删除
            window.dataLayer = window.dataLayer || [];
            function gtag(){dataLayer.push(arguments);}
            gtag('js', new Date());

            gtag('config', 'G-B7351EYS04');
        </script>
    <meta name="description" content="阿俊博客|记录一个程序员的成长历程 阿俊博客(blog.zhequtao.com)记录一个后端程序员的成长历程,分享他的学习笔记和心得,还有些开发必备技巧哦! java golang web html dev开发">
    <link rel="preload" href="/assets/css/0.styles.a3df589e.css" as="style"><link rel="preload" href="/assets/js/app.cb35c8f6.js" as="script"><link rel="preload" href="/assets/js/2.063846a6.js" as="script"><link rel="preload" href="/assets/js/4.1ffb4609.js" as="script"><link rel="preload" href="/assets/js/590.1a06b2a0.js" as="script"><link rel="prefetch" href="/assets/js/10.5629fe4d.js"><link rel="prefetch" href="/assets/js/100.8417ccea.js"><link rel="prefetch" href="/assets/js/101.37fdb377.js"><link rel="prefetch" href="/assets/js/102.848b686d.js"><link rel="prefetch" href="/assets/js/103.6a2489a0.js"><link rel="prefetch" href="/assets/js/104.99e8899f.js"><link rel="prefetch" href="/assets/js/105.8b741763.js"><link rel="prefetch" href="/assets/js/106.08163715.js"><link rel="prefetch" href="/assets/js/107.21801349.js"><link rel="prefetch" href="/assets/js/108.e3a2892b.js"><link rel="prefetch" href="/assets/js/109.dab5618c.js"><link rel="prefetch" href="/assets/js/11.cddc1623.js"><link rel="prefetch" href="/assets/js/110.f5249b39.js"><link rel="prefetch" href="/assets/js/111.9abf8bce.js"><link rel="prefetch" href="/assets/js/112.8eb86924.js"><link rel="prefetch" href="/assets/js/113.c180ed46.js"><link rel="prefetch" href="/assets/js/114.2370f5b5.js"><link rel="prefetch" href="/assets/js/115.88dc3fd8.js"><link rel="prefetch" href="/assets/js/116.63403600.js"><link rel="prefetch" href="/assets/js/117.83674aa0.js"><link rel="prefetch" href="/assets/js/118.9c58c685.js"><link rel="prefetch" href="/assets/js/119.621f9a70.js"><link rel="prefetch" href="/assets/js/12.ff3d03f5.js"><link rel="prefetch" href="/assets/js/120.a6d2b7f7.js"><link rel="prefetch" href="/assets/js/121.ae0ce9d1.js"><link rel="prefetch" href="/assets/js/122.de240e6d.js"><link rel="prefetch" href="/assets/js/123.b043e3e4.js"><link rel="prefetch" href="/assets/js/124.550af937.js"><link rel="prefetch" href="/assets/js/125.6f06b34c.js"><link rel="prefetch" href="/assets/js/126.38d64604.js"><link rel="prefetch" href="/assets/js/127.a948db56.js"><link rel="prefetch" href="/assets/js/128.4ca789e0.js"><link rel="prefetch" href="/assets/js/129.59be4505.js"><link rel="prefetch" href="/assets/js/13.e9f75cc3.js"><link rel="prefetch" href="/assets/js/130.ddae76a9.js"><link rel="prefetch" href="/assets/js/131.d969c265.js"><link rel="prefetch" href="/assets/js/132.5f43ce5c.js"><link rel="prefetch" href="/assets/js/133.b651af22.js"><link rel="prefetch" href="/assets/js/134.2499236a.js"><link rel="prefetch" href="/assets/js/135.4180596c.js"><link rel="prefetch" href="/assets/js/136.2c309233.js"><link rel="prefetch" href="/assets/js/137.72ca42dd.js"><link rel="prefetch" href="/assets/js/138.3772cd38.js"><link rel="prefetch" href="/assets/js/139.1d0a53da.js"><link rel="prefetch" href="/assets/js/14.be4b89ed.js"><link rel="prefetch" href="/assets/js/140.22368353.js"><link rel="prefetch" href="/assets/js/141.a14aef3c.js"><link rel="prefetch" href="/assets/js/142.2041ce3a.js"><link rel="prefetch" href="/assets/js/143.377262ec.js"><link rel="prefetch" href="/assets/js/144.8fcbc368.js"><link rel="prefetch" href="/assets/js/145.46da36bf.js"><link rel="prefetch" href="/assets/js/146.eceae0d9.js"><link rel="prefetch" href="/assets/js/147.e1bd6531.js"><link rel="prefetch" href="/assets/js/148.76f193ce.js"><link rel="prefetch" href="/assets/js/149.587bd581.js"><link rel="prefetch" href="/assets/js/15.e3219ac0.js"><link rel="prefetch" href="/assets/js/150.169c71e0.js"><link rel="prefetch" href="/assets/js/151.384021ea.js"><link rel="prefetch" href="/assets/js/152.0f20cf03.js"><link rel="prefetch" href="/assets/js/153.fd94af1e.js"><link rel="prefetch" href="/assets/js/154.a550f5dc.js"><link rel="prefetch" href="/assets/js/155.ba1ae86e.js"><link rel="prefetch" href="/assets/js/156.3b98ded0.js"><link rel="prefetch" href="/assets/js/157.cd378596.js"><link rel="prefetch" href="/assets/js/158.c138d0df.js"><link rel="prefetch" href="/assets/js/159.2635f7f4.js"><link rel="prefetch" href="/assets/js/16.c9c35d42.js"><link rel="prefetch" href="/assets/js/160.d51b4126.js"><link rel="prefetch" href="/assets/js/161.5dc29e7b.js"><link rel="prefetch" href="/assets/js/162.f257a92b.js"><link rel="prefetch" href="/assets/js/163.9eb75e40.js"><link rel="prefetch" href="/assets/js/164.8fb1e22b.js"><link rel="prefetch" href="/assets/js/165.49255503.js"><link rel="prefetch" href="/assets/js/166.e54539e6.js"><link rel="prefetch" href="/assets/js/167.a06acdb0.js"><link rel="prefetch" href="/assets/js/168.948ab620.js"><link rel="prefetch" href="/assets/js/169.7d756812.js"><link rel="prefetch" href="/assets/js/17.81067bc8.js"><link rel="prefetch" href="/assets/js/170.ffe63330.js"><link rel="prefetch" href="/assets/js/171.835398b9.js"><link rel="prefetch" href="/assets/js/172.3987be39.js"><link rel="prefetch" href="/assets/js/173.e3cedb8a.js"><link rel="prefetch" href="/assets/js/174.11a32588.js"><link rel="prefetch" href="/assets/js/175.da6f9782.js"><link rel="prefetch" href="/assets/js/176.50e55edc.js"><link rel="prefetch" href="/assets/js/177.a89a1d17.js"><link rel="prefetch" href="/assets/js/178.7ad3ce84.js"><link rel="prefetch" href="/assets/js/179.98a1343b.js"><link rel="prefetch" href="/assets/js/18.b67caf4c.js"><link rel="prefetch" href="/assets/js/180.4e98599b.js"><link rel="prefetch" href="/assets/js/181.dd885afd.js"><link rel="prefetch" href="/assets/js/182.789990c8.js"><link rel="prefetch" href="/assets/js/183.f11ca2fd.js"><link rel="prefetch" href="/assets/js/184.fcf128ec.js"><link rel="prefetch" href="/assets/js/185.54e1e9b6.js"><link rel="prefetch" href="/assets/js/186.db91021a.js"><link rel="prefetch" href="/assets/js/187.5309dd42.js"><link rel="prefetch" href="/assets/js/188.615821ea.js"><link rel="prefetch" href="/assets/js/189.f23f1d42.js"><link rel="prefetch" href="/assets/js/19.294a011c.js"><link rel="prefetch" href="/assets/js/190.efe894bc.js"><link rel="prefetch" href="/assets/js/191.0d14904a.js"><link rel="prefetch" href="/assets/js/192.efd25a9e.js"><link rel="prefetch" href="/assets/js/193.88151f34.js"><link rel="prefetch" href="/assets/js/194.efedce14.js"><link rel="prefetch" href="/assets/js/195.03ac15bb.js"><link rel="prefetch" href="/assets/js/196.fbc18389.js"><link rel="prefetch" href="/assets/js/197.0257ab5e.js"><link rel="prefetch" href="/assets/js/198.f1de2817.js"><link rel="prefetch" href="/assets/js/199.8f1c166b.js"><link rel="prefetch" href="/assets/js/20.1a41edb1.js"><link rel="prefetch" href="/assets/js/200.4af99727.js"><link rel="prefetch" href="/assets/js/201.9f8caef1.js"><link rel="prefetch" href="/assets/js/202.6f07d705.js"><link rel="prefetch" href="/assets/js/203.5012fe50.js"><link rel="prefetch" href="/assets/js/204.3a0dfd8e.js"><link rel="prefetch" href="/assets/js/205.0ba9b606.js"><link rel="prefetch" href="/assets/js/206.663a49ec.js"><link rel="prefetch" href="/assets/js/207.b2406149.js"><link rel="prefetch" href="/assets/js/208.b0b2dfd8.js"><link rel="prefetch" href="/assets/js/209.41a0ec9f.js"><link rel="prefetch" href="/assets/js/21.b6ea6f9a.js"><link rel="prefetch" href="/assets/js/210.6f36beb8.js"><link rel="prefetch" href="/assets/js/211.90ef6e5c.js"><link rel="prefetch" href="/assets/js/212.42339063.js"><link rel="prefetch" href="/assets/js/213.a2bfeda9.js"><link rel="prefetch" href="/assets/js/214.5c7eb42a.js"><link rel="prefetch" href="/assets/js/215.18260ae5.js"><link rel="prefetch" href="/assets/js/216.7678fd29.js"><link rel="prefetch" href="/assets/js/217.9d936f88.js"><link rel="prefetch" href="/assets/js/218.bac403c4.js"><link rel="prefetch" href="/assets/js/219.9d8cb16b.js"><link rel="prefetch" href="/assets/js/22.367cc253.js"><link rel="prefetch" href="/assets/js/220.375b7707.js"><link rel="prefetch" href="/assets/js/221.3e400da4.js"><link rel="prefetch" href="/assets/js/222.8fbd2857.js"><link rel="prefetch" href="/assets/js/223.76a10075.js"><link rel="prefetch" href="/assets/js/224.ff94cc4e.js"><link rel="prefetch" href="/assets/js/225.d4688ca6.js"><link rel="prefetch" href="/assets/js/226.b13b18af.js"><link rel="prefetch" href="/assets/js/227.288d0f96.js"><link rel="prefetch" href="/assets/js/228.60430ac0.js"><link rel="prefetch" href="/assets/js/229.da728342.js"><link rel="prefetch" href="/assets/js/23.bc3de730.js"><link rel="prefetch" href="/assets/js/230.d8417d20.js"><link rel="prefetch" href="/assets/js/231.6a9ce0b4.js"><link rel="prefetch" href="/assets/js/232.7611b413.js"><link rel="prefetch" href="/assets/js/233.94712cf9.js"><link rel="prefetch" href="/assets/js/234.93888298.js"><link rel="prefetch" href="/assets/js/235.62506981.js"><link rel="prefetch" href="/assets/js/236.5a055c7c.js"><link rel="prefetch" href="/assets/js/237.0a6c4902.js"><link rel="prefetch" href="/assets/js/238.e5f37663.js"><link rel="prefetch" href="/assets/js/239.331bba86.js"><link rel="prefetch" href="/assets/js/24.82f0901c.js"><link rel="prefetch" href="/assets/js/240.59591cfc.js"><link rel="prefetch" href="/assets/js/241.ce671f47.js"><link rel="prefetch" href="/assets/js/242.fb2542fa.js"><link rel="prefetch" href="/assets/js/243.74abc73d.js"><link rel="prefetch" href="/assets/js/244.ecd707b2.js"><link rel="prefetch" href="/assets/js/245.d728367b.js"><link rel="prefetch" href="/assets/js/246.9270e7fe.js"><link rel="prefetch" href="/assets/js/247.a421ba15.js"><link rel="prefetch" href="/assets/js/248.e759132e.js"><link rel="prefetch" href="/assets/js/249.3077fb46.js"><link rel="prefetch" href="/assets/js/25.95c551dc.js"><link rel="prefetch" href="/assets/js/250.5fe4dd03.js"><link rel="prefetch" href="/assets/js/251.4b8fe76c.js"><link rel="prefetch" href="/assets/js/252.0d1cf7ea.js"><link rel="prefetch" href="/assets/js/253.89d16ba0.js"><link rel="prefetch" href="/assets/js/254.8d1afc68.js"><link rel="prefetch" href="/assets/js/255.64e680cb.js"><link rel="prefetch" href="/assets/js/256.9defbd0e.js"><link rel="prefetch" href="/assets/js/257.1fef24fd.js"><link rel="prefetch" href="/assets/js/258.4e205286.js"><link rel="prefetch" href="/assets/js/259.f3d56efc.js"><link rel="prefetch" href="/assets/js/26.7fe56bac.js"><link rel="prefetch" href="/assets/js/260.378299e6.js"><link rel="prefetch" href="/assets/js/261.8e4fb397.js"><link rel="prefetch" href="/assets/js/262.54595d98.js"><link rel="prefetch" href="/assets/js/263.fb333dc1.js"><link rel="prefetch" href="/assets/js/264.13d13301.js"><link rel="prefetch" href="/assets/js/265.c85104bc.js"><link rel="prefetch" href="/assets/js/266.9f3d1f1c.js"><link rel="prefetch" href="/assets/js/267.fb392815.js"><link rel="prefetch" href="/assets/js/268.add7eacb.js"><link rel="prefetch" href="/assets/js/269.7e49fcc7.js"><link rel="prefetch" href="/assets/js/27.5547e5f0.js"><link rel="prefetch" href="/assets/js/270.22342070.js"><link rel="prefetch" href="/assets/js/271.59917b1a.js"><link rel="prefetch" href="/assets/js/272.4a4751d5.js"><link rel="prefetch" href="/assets/js/273.f4c2fa5c.js"><link rel="prefetch" href="/assets/js/274.1208bc94.js"><link rel="prefetch" href="/assets/js/275.f6528753.js"><link rel="prefetch" href="/assets/js/276.a68772cd.js"><link rel="prefetch" href="/assets/js/277.f154ab32.js"><link rel="prefetch" href="/assets/js/278.bb616a63.js"><link rel="prefetch" href="/assets/js/279.6a356cff.js"><link rel="prefetch" href="/assets/js/28.b08b2847.js"><link rel="prefetch" href="/assets/js/280.5afc20fc.js"><link rel="prefetch" href="/assets/js/281.3cd86225.js"><link rel="prefetch" href="/assets/js/282.3ecb92aa.js"><link rel="prefetch" href="/assets/js/283.85159ea7.js"><link rel="prefetch" href="/assets/js/284.b6545c68.js"><link rel="prefetch" href="/assets/js/285.5e83ee29.js"><link rel="prefetch" href="/assets/js/286.2aad298d.js"><link rel="prefetch" href="/assets/js/287.3ca76244.js"><link rel="prefetch" href="/assets/js/288.5b17963d.js"><link rel="prefetch" href="/assets/js/289.f47a759b.js"><link rel="prefetch" href="/assets/js/29.562dbf7d.js"><link rel="prefetch" href="/assets/js/290.36b603c7.js"><link rel="prefetch" href="/assets/js/291.a9bd8951.js"><link rel="prefetch" href="/assets/js/292.dfc54f28.js"><link rel="prefetch" href="/assets/js/293.2d061212.js"><link rel="prefetch" href="/assets/js/294.6d564a9e.js"><link rel="prefetch" href="/assets/js/295.a31dd593.js"><link rel="prefetch" href="/assets/js/296.3101e5e8.js"><link rel="prefetch" href="/assets/js/297.7efde936.js"><link rel="prefetch" href="/assets/js/298.8bb3aa68.js"><link rel="prefetch" href="/assets/js/299.8b51e6bd.js"><link rel="prefetch" href="/assets/js/3.76257eb7.js"><link rel="prefetch" href="/assets/js/30.221adea2.js"><link rel="prefetch" href="/assets/js/300.62effe45.js"><link rel="prefetch" href="/assets/js/301.98c863c5.js"><link rel="prefetch" href="/assets/js/302.e3493ab2.js"><link rel="prefetch" href="/assets/js/303.800c8028.js"><link rel="prefetch" href="/assets/js/304.b6f08986.js"><link rel="prefetch" href="/assets/js/305.09af356b.js"><link rel="prefetch" href="/assets/js/306.d0d3589d.js"><link rel="prefetch" href="/assets/js/307.ee2fd249.js"><link rel="prefetch" href="/assets/js/308.f3f76368.js"><link rel="prefetch" href="/assets/js/309.d2b5ce40.js"><link rel="prefetch" href="/assets/js/31.334fc8bb.js"><link rel="prefetch" href="/assets/js/310.b4fa2feb.js"><link rel="prefetch" href="/assets/js/311.7d747ef3.js"><link rel="prefetch" href="/assets/js/312.89bdff40.js"><link rel="prefetch" href="/assets/js/313.875b82fe.js"><link rel="prefetch" href="/assets/js/314.0bbe51c4.js"><link rel="prefetch" href="/assets/js/315.cc07dbcf.js"><link rel="prefetch" href="/assets/js/316.bc72b152.js"><link rel="prefetch" href="/assets/js/317.55462812.js"><link rel="prefetch" href="/assets/js/318.a158fda0.js"><link rel="prefetch" href="/assets/js/319.b03a5bd2.js"><link rel="prefetch" href="/assets/js/32.d6826b16.js"><link rel="prefetch" href="/assets/js/320.a5bd19b0.js"><link rel="prefetch" href="/assets/js/321.4f9faaa7.js"><link rel="prefetch" href="/assets/js/322.dbd3b4fa.js"><link rel="prefetch" href="/assets/js/323.a04e2062.js"><link rel="prefetch" href="/assets/js/324.c45b46cf.js"><link rel="prefetch" href="/assets/js/325.cd1460c4.js"><link rel="prefetch" href="/assets/js/326.bd90ef85.js"><link rel="prefetch" href="/assets/js/327.8bf38ef7.js"><link rel="prefetch" href="/assets/js/328.99e9aed3.js"><link rel="prefetch" href="/assets/js/329.de0012cb.js"><link rel="prefetch" href="/assets/js/33.b1059062.js"><link rel="prefetch" href="/assets/js/330.59f11391.js"><link rel="prefetch" href="/assets/js/331.6d16a13c.js"><link rel="prefetch" href="/assets/js/332.922ca235.js"><link rel="prefetch" href="/assets/js/333.c94c3602.js"><link rel="prefetch" href="/assets/js/334.77e02010.js"><link rel="prefetch" href="/assets/js/335.1e0c4f7b.js"><link rel="prefetch" href="/assets/js/336.5675dc4f.js"><link rel="prefetch" href="/assets/js/337.bb6e11dc.js"><link rel="prefetch" href="/assets/js/338.294981e9.js"><link rel="prefetch" href="/assets/js/339.d0376372.js"><link rel="prefetch" href="/assets/js/34.0c7d5782.js"><link rel="prefetch" href="/assets/js/340.64596428.js"><link rel="prefetch" href="/assets/js/341.ec0f9409.js"><link rel="prefetch" href="/assets/js/342.7abc47c4.js"><link rel="prefetch" href="/assets/js/343.4262d486.js"><link rel="prefetch" href="/assets/js/344.8729ad8c.js"><link rel="prefetch" href="/assets/js/345.c210d888.js"><link rel="prefetch" href="/assets/js/346.6f42f7cb.js"><link rel="prefetch" href="/assets/js/347.81b41ae5.js"><link rel="prefetch" href="/assets/js/348.07eca37c.js"><link rel="prefetch" href="/assets/js/349.8019d6f3.js"><link rel="prefetch" href="/assets/js/35.ae14e37f.js"><link rel="prefetch" href="/assets/js/350.57da2e7b.js"><link rel="prefetch" href="/assets/js/351.2e99afdf.js"><link rel="prefetch" href="/assets/js/352.67dd88b7.js"><link rel="prefetch" href="/assets/js/353.15b9f624.js"><link rel="prefetch" href="/assets/js/354.a63e8432.js"><link rel="prefetch" href="/assets/js/355.bbc16ee9.js"><link rel="prefetch" href="/assets/js/356.ff63d3bb.js"><link rel="prefetch" href="/assets/js/357.4fb2d941.js"><link rel="prefetch" href="/assets/js/358.55182977.js"><link rel="prefetch" href="/assets/js/359.265c3d26.js"><link rel="prefetch" href="/assets/js/36.b9ed4cf1.js"><link rel="prefetch" href="/assets/js/360.ced80eb3.js"><link rel="prefetch" href="/assets/js/361.afe6ba84.js"><link rel="prefetch" href="/assets/js/362.c2b62518.js"><link rel="prefetch" href="/assets/js/363.0c4a4800.js"><link rel="prefetch" href="/assets/js/364.ce60291b.js"><link rel="prefetch" href="/assets/js/365.0e3a61f9.js"><link rel="prefetch" href="/assets/js/366.d53ccb03.js"><link rel="prefetch" href="/assets/js/367.689e464d.js"><link rel="prefetch" href="/assets/js/368.418c571f.js"><link rel="prefetch" href="/assets/js/369.c2fff3c8.js"><link rel="prefetch" href="/assets/js/37.a021ac57.js"><link rel="prefetch" href="/assets/js/370.a932b958.js"><link rel="prefetch" href="/assets/js/371.7d153241.js"><link rel="prefetch" href="/assets/js/372.fb9878fa.js"><link rel="prefetch" href="/assets/js/373.85772e03.js"><link rel="prefetch" href="/assets/js/374.b4a8b1b6.js"><link rel="prefetch" href="/assets/js/375.32f70596.js"><link rel="prefetch" href="/assets/js/376.a16d79a8.js"><link rel="prefetch" href="/assets/js/377.c996b7e1.js"><link rel="prefetch" href="/assets/js/378.d37d15c7.js"><link rel="prefetch" href="/assets/js/379.b81ba7dd.js"><link rel="prefetch" href="/assets/js/38.89138658.js"><link rel="prefetch" href="/assets/js/380.524c9b31.js"><link rel="prefetch" href="/assets/js/381.7ebfd6db.js"><link rel="prefetch" href="/assets/js/382.29edda0f.js"><link rel="prefetch" href="/assets/js/383.9642a212.js"><link rel="prefetch" href="/assets/js/384.086e3b42.js"><link rel="prefetch" href="/assets/js/385.a9bb46a8.js"><link rel="prefetch" href="/assets/js/386.f2561a39.js"><link rel="prefetch" href="/assets/js/387.ba9b6aaa.js"><link rel="prefetch" href="/assets/js/388.e0ace495.js"><link rel="prefetch" href="/assets/js/389.ba8c09dd.js"><link rel="prefetch" href="/assets/js/39.04f331e3.js"><link rel="prefetch" href="/assets/js/390.de1bb48b.js"><link rel="prefetch" href="/assets/js/391.7cc6edeb.js"><link rel="prefetch" href="/assets/js/392.0493a6f7.js"><link rel="prefetch" href="/assets/js/393.ba2d3e62.js"><link rel="prefetch" href="/assets/js/394.b3aa9224.js"><link rel="prefetch" href="/assets/js/395.f4df3a60.js"><link rel="prefetch" href="/assets/js/396.09644790.js"><link rel="prefetch" href="/assets/js/397.76163964.js"><link rel="prefetch" href="/assets/js/398.377fd8fc.js"><link rel="prefetch" href="/assets/js/399.39059be5.js"><link rel="prefetch" href="/assets/js/40.a21abf05.js"><link rel="prefetch" href="/assets/js/400.559a547f.js"><link rel="prefetch" href="/assets/js/401.bc6d738c.js"><link rel="prefetch" href="/assets/js/402.c504ed7b.js"><link rel="prefetch" href="/assets/js/403.77a0af9c.js"><link rel="prefetch" href="/assets/js/404.0f408cc6.js"><link rel="prefetch" href="/assets/js/405.90b0bab6.js"><link rel="prefetch" href="/assets/js/406.01b11432.js"><link rel="prefetch" href="/assets/js/407.58991ce5.js"><link rel="prefetch" href="/assets/js/408.9806278e.js"><link rel="prefetch" href="/assets/js/409.8d9b4bb3.js"><link rel="prefetch" href="/assets/js/41.6bcfc592.js"><link rel="prefetch" href="/assets/js/410.02dee620.js"><link rel="prefetch" href="/assets/js/411.3d12d3a6.js"><link rel="prefetch" href="/assets/js/412.37a62624.js"><link rel="prefetch" href="/assets/js/413.bda7ca34.js"><link rel="prefetch" href="/assets/js/414.2abb6547.js"><link rel="prefetch" href="/assets/js/415.1d271923.js"><link rel="prefetch" href="/assets/js/416.d1b11dfe.js"><link rel="prefetch" href="/assets/js/417.fcbf07ff.js"><link rel="prefetch" href="/assets/js/418.524d40ba.js"><link rel="prefetch" href="/assets/js/419.f264e463.js"><link rel="prefetch" href="/assets/js/42.ec85a270.js"><link rel="prefetch" href="/assets/js/420.8b99e60d.js"><link rel="prefetch" href="/assets/js/421.c697d876.js"><link rel="prefetch" href="/assets/js/422.4af54ae0.js"><link rel="prefetch" href="/assets/js/423.b75f24ff.js"><link rel="prefetch" href="/assets/js/424.ea80054f.js"><link rel="prefetch" href="/assets/js/425.804b48b0.js"><link rel="prefetch" href="/assets/js/426.ffed8383.js"><link rel="prefetch" href="/assets/js/427.2040bd22.js"><link rel="prefetch" href="/assets/js/428.b878fb56.js"><link rel="prefetch" href="/assets/js/429.f81fd922.js"><link rel="prefetch" href="/assets/js/43.c174449d.js"><link rel="prefetch" href="/assets/js/430.16f328ab.js"><link rel="prefetch" href="/assets/js/431.56f11924.js"><link rel="prefetch" href="/assets/js/432.e4c77710.js"><link rel="prefetch" href="/assets/js/433.29acf14b.js"><link rel="prefetch" href="/assets/js/434.33ee22fc.js"><link rel="prefetch" href="/assets/js/435.516f7600.js"><link rel="prefetch" href="/assets/js/436.28ae526a.js"><link rel="prefetch" href="/assets/js/437.b9bac473.js"><link rel="prefetch" href="/assets/js/438.711bd934.js"><link rel="prefetch" href="/assets/js/439.fe5f28bd.js"><link rel="prefetch" href="/assets/js/44.8704338b.js"><link rel="prefetch" href="/assets/js/440.b2f48747.js"><link rel="prefetch" href="/assets/js/441.4862a724.js"><link rel="prefetch" href="/assets/js/442.751f8ae7.js"><link rel="prefetch" href="/assets/js/443.9998dbe9.js"><link rel="prefetch" href="/assets/js/444.23d3f688.js"><link rel="prefetch" href="/assets/js/445.72419f35.js"><link rel="prefetch" href="/assets/js/446.21e02b7b.js"><link rel="prefetch" href="/assets/js/447.6bd039e5.js"><link rel="prefetch" href="/assets/js/448.af29ff31.js"><link rel="prefetch" href="/assets/js/449.bbdca196.js"><link rel="prefetch" href="/assets/js/45.f8a2a3b3.js"><link rel="prefetch" href="/assets/js/450.32e470b8.js"><link rel="prefetch" href="/assets/js/451.f4c8ff6a.js"><link rel="prefetch" href="/assets/js/452.c0ec8943.js"><link rel="prefetch" href="/assets/js/453.5af475c6.js"><link rel="prefetch" href="/assets/js/454.567aa6c1.js"><link rel="prefetch" href="/assets/js/455.326072d1.js"><link rel="prefetch" href="/assets/js/456.64b88847.js"><link rel="prefetch" href="/assets/js/457.61c91559.js"><link rel="prefetch" href="/assets/js/458.4f4d43d6.js"><link rel="prefetch" href="/assets/js/459.a88891cb.js"><link rel="prefetch" href="/assets/js/46.90855a0f.js"><link rel="prefetch" href="/assets/js/460.fb9e15f6.js"><link rel="prefetch" href="/assets/js/461.998a4991.js"><link rel="prefetch" href="/assets/js/462.56ecfb03.js"><link rel="prefetch" href="/assets/js/463.582cd053.js"><link rel="prefetch" href="/assets/js/464.f12a0050.js"><link rel="prefetch" href="/assets/js/465.cc3b08a8.js"><link rel="prefetch" href="/assets/js/466.ea7e4ce2.js"><link rel="prefetch" href="/assets/js/467.58700d93.js"><link rel="prefetch" href="/assets/js/468.bb9998cd.js"><link rel="prefetch" href="/assets/js/469.232d261d.js"><link rel="prefetch" href="/assets/js/47.02516d33.js"><link rel="prefetch" href="/assets/js/470.90ab2be8.js"><link rel="prefetch" href="/assets/js/471.3652fec6.js"><link rel="prefetch" href="/assets/js/472.bc7517c9.js"><link rel="prefetch" href="/assets/js/473.27b7892f.js"><link rel="prefetch" href="/assets/js/474.0d488768.js"><link rel="prefetch" href="/assets/js/475.bc33cf17.js"><link rel="prefetch" href="/assets/js/476.76ce67eb.js"><link rel="prefetch" href="/assets/js/477.a20657f6.js"><link rel="prefetch" href="/assets/js/478.8905ef26.js"><link rel="prefetch" href="/assets/js/479.23ec3dc9.js"><link rel="prefetch" href="/assets/js/48.8d38983c.js"><link rel="prefetch" href="/assets/js/480.105bf58e.js"><link rel="prefetch" href="/assets/js/481.d62a3699.js"><link rel="prefetch" href="/assets/js/482.6cb10cdb.js"><link rel="prefetch" href="/assets/js/483.15b9ef53.js"><link rel="prefetch" href="/assets/js/484.794aff9c.js"><link rel="prefetch" href="/assets/js/485.3e6ef3cb.js"><link rel="prefetch" href="/assets/js/486.fc20eb22.js"><link rel="prefetch" href="/assets/js/487.2a4f7d47.js"><link rel="prefetch" href="/assets/js/488.24ba9af5.js"><link rel="prefetch" href="/assets/js/489.d451928e.js"><link rel="prefetch" href="/assets/js/49.bd70c59c.js"><link rel="prefetch" href="/assets/js/490.d83e4fc7.js"><link rel="prefetch" href="/assets/js/491.0210a738.js"><link rel="prefetch" href="/assets/js/492.3a69cd85.js"><link rel="prefetch" href="/assets/js/493.89d2920f.js"><link rel="prefetch" href="/assets/js/494.b7d1c15e.js"><link rel="prefetch" href="/assets/js/495.7ccfe17f.js"><link rel="prefetch" href="/assets/js/496.0d6c912a.js"><link rel="prefetch" href="/assets/js/497.087ca2c6.js"><link rel="prefetch" href="/assets/js/498.f2b3c894.js"><link rel="prefetch" href="/assets/js/499.e741f312.js"><link rel="prefetch" href="/assets/js/5.405f2620.js"><link rel="prefetch" href="/assets/js/50.9df07553.js"><link rel="prefetch" href="/assets/js/500.cb9babb9.js"><link rel="prefetch" href="/assets/js/501.63a859b6.js"><link rel="prefetch" href="/assets/js/502.f346b273.js"><link rel="prefetch" href="/assets/js/503.cdcd3de3.js"><link rel="prefetch" href="/assets/js/504.e83ec450.js"><link rel="prefetch" href="/assets/js/505.95f18293.js"><link rel="prefetch" href="/assets/js/506.02060a3c.js"><link rel="prefetch" href="/assets/js/507.859d6ae4.js"><link rel="prefetch" href="/assets/js/508.9724d886.js"><link rel="prefetch" href="/assets/js/509.e17dd53e.js"><link rel="prefetch" href="/assets/js/51.3dfc6350.js"><link rel="prefetch" href="/assets/js/510.ea0c942f.js"><link rel="prefetch" href="/assets/js/511.64a7f9a8.js"><link rel="prefetch" href="/assets/js/512.6cce418a.js"><link rel="prefetch" href="/assets/js/513.ea813fe8.js"><link rel="prefetch" href="/assets/js/514.10242470.js"><link rel="prefetch" href="/assets/js/515.5308bdf6.js"><link rel="prefetch" href="/assets/js/516.dfb113bc.js"><link rel="prefetch" href="/assets/js/517.96c71069.js"><link rel="prefetch" href="/assets/js/518.5594480e.js"><link rel="prefetch" href="/assets/js/519.91e848ae.js"><link rel="prefetch" href="/assets/js/52.dd230cd7.js"><link rel="prefetch" href="/assets/js/520.ebe69ce9.js"><link rel="prefetch" href="/assets/js/521.92b80342.js"><link rel="prefetch" href="/assets/js/522.6ce581ea.js"><link rel="prefetch" href="/assets/js/523.40b7f2f9.js"><link rel="prefetch" href="/assets/js/524.4ef256d3.js"><link rel="prefetch" href="/assets/js/525.19741e4a.js"><link rel="prefetch" href="/assets/js/526.e5de1675.js"><link rel="prefetch" href="/assets/js/527.9a79cd42.js"><link rel="prefetch" href="/assets/js/528.72732eb8.js"><link rel="prefetch" href="/assets/js/529.2ea03e45.js"><link rel="prefetch" href="/assets/js/53.a349c565.js"><link rel="prefetch" href="/assets/js/530.5d1103e7.js"><link rel="prefetch" href="/assets/js/531.82b032d6.js"><link rel="prefetch" href="/assets/js/532.12ee4beb.js"><link rel="prefetch" href="/assets/js/533.db9a90f7.js"><link rel="prefetch" href="/assets/js/534.fe139db4.js"><link rel="prefetch" href="/assets/js/535.056a6fbb.js"><link rel="prefetch" href="/assets/js/536.6bf85c15.js"><link rel="prefetch" href="/assets/js/537.6e2e1ccf.js"><link rel="prefetch" href="/assets/js/538.54eddb3b.js"><link rel="prefetch" href="/assets/js/539.6f00a207.js"><link rel="prefetch" href="/assets/js/54.e292b52f.js"><link rel="prefetch" href="/assets/js/540.9c956205.js"><link rel="prefetch" href="/assets/js/541.50f41228.js"><link rel="prefetch" href="/assets/js/542.a2b1879e.js"><link rel="prefetch" href="/assets/js/543.4e5e17c9.js"><link rel="prefetch" href="/assets/js/544.63d85227.js"><link rel="prefetch" href="/assets/js/545.c3923644.js"><link rel="prefetch" href="/assets/js/546.e64ad073.js"><link rel="prefetch" href="/assets/js/547.35a8752d.js"><link rel="prefetch" href="/assets/js/548.b62a1348.js"><link rel="prefetch" href="/assets/js/549.369e2ea0.js"><link rel="prefetch" href="/assets/js/55.21bb2983.js"><link rel="prefetch" href="/assets/js/550.b4632248.js"><link rel="prefetch" href="/assets/js/551.18f1879c.js"><link rel="prefetch" href="/assets/js/552.162e63cd.js"><link rel="prefetch" href="/assets/js/553.6998130e.js"><link rel="prefetch" href="/assets/js/554.89126c3d.js"><link rel="prefetch" href="/assets/js/555.a5f63b8a.js"><link rel="prefetch" href="/assets/js/556.35381cef.js"><link rel="prefetch" href="/assets/js/557.ceeecf52.js"><link rel="prefetch" href="/assets/js/558.253e6e4f.js"><link rel="prefetch" href="/assets/js/559.5cfbb773.js"><link rel="prefetch" href="/assets/js/56.5382d6b4.js"><link rel="prefetch" href="/assets/js/560.cca52f22.js"><link rel="prefetch" href="/assets/js/561.ea8d1141.js"><link rel="prefetch" href="/assets/js/562.c32e7b96.js"><link rel="prefetch" href="/assets/js/563.9bdd42e7.js"><link rel="prefetch" href="/assets/js/564.b3b87ec0.js"><link rel="prefetch" href="/assets/js/565.4be0d0f7.js"><link rel="prefetch" href="/assets/js/566.9f379d12.js"><link rel="prefetch" href="/assets/js/567.261e3181.js"><link rel="prefetch" href="/assets/js/568.4229e365.js"><link rel="prefetch" href="/assets/js/569.e662c167.js"><link rel="prefetch" href="/assets/js/57.8129f7e0.js"><link rel="prefetch" href="/assets/js/570.97ff6423.js"><link rel="prefetch" href="/assets/js/571.de1377cd.js"><link rel="prefetch" href="/assets/js/572.48b8400b.js"><link rel="prefetch" href="/assets/js/573.8251ebaf.js"><link rel="prefetch" href="/assets/js/574.ec3d6c1e.js"><link rel="prefetch" href="/assets/js/575.b0d429a1.js"><link rel="prefetch" href="/assets/js/576.98ce9170.js"><link rel="prefetch" href="/assets/js/577.85fc2017.js"><link rel="prefetch" href="/assets/js/578.1393ac7f.js"><link rel="prefetch" href="/assets/js/579.1340e178.js"><link rel="prefetch" href="/assets/js/58.85ac2740.js"><link rel="prefetch" href="/assets/js/580.a5979445.js"><link rel="prefetch" href="/assets/js/581.effdc269.js"><link rel="prefetch" href="/assets/js/582.13276063.js"><link rel="prefetch" href="/assets/js/583.357a2443.js"><link rel="prefetch" href="/assets/js/584.338ef731.js"><link rel="prefetch" href="/assets/js/585.87932741.js"><link rel="prefetch" href="/assets/js/586.b1d6e000.js"><link rel="prefetch" href="/assets/js/587.ed5b8377.js"><link rel="prefetch" href="/assets/js/588.16d2c418.js"><link rel="prefetch" href="/assets/js/589.c96fd7fa.js"><link rel="prefetch" href="/assets/js/59.0b225757.js"><link rel="prefetch" href="/assets/js/591.0efd886c.js"><link rel="prefetch" href="/assets/js/592.b22d0ef5.js"><link rel="prefetch" href="/assets/js/593.84ed6ebe.js"><link rel="prefetch" href="/assets/js/594.cd721b88.js"><link rel="prefetch" href="/assets/js/595.2400817a.js"><link rel="prefetch" href="/assets/js/596.f2c512d1.js"><link rel="prefetch" href="/assets/js/597.df10ec57.js"><link rel="prefetch" href="/assets/js/598.61ef6a7d.js"><link rel="prefetch" href="/assets/js/599.0bebb562.js"><link rel="prefetch" href="/assets/js/6.29d112b1.js"><link rel="prefetch" href="/assets/js/60.3bbab51d.js"><link rel="prefetch" href="/assets/js/600.1ea7596f.js"><link rel="prefetch" href="/assets/js/601.f2642bb3.js"><link rel="prefetch" href="/assets/js/602.400cc987.js"><link rel="prefetch" href="/assets/js/603.db77d173.js"><link rel="prefetch" href="/assets/js/604.0ffa30ae.js"><link rel="prefetch" href="/assets/js/605.32ca9607.js"><link rel="prefetch" href="/assets/js/606.8c1e5683.js"><link rel="prefetch" href="/assets/js/607.9c9463ef.js"><link rel="prefetch" href="/assets/js/608.d5efa77e.js"><link rel="prefetch" href="/assets/js/609.5bbffb7d.js"><link rel="prefetch" href="/assets/js/61.f7301486.js"><link rel="prefetch" href="/assets/js/610.5b0fc9da.js"><link rel="prefetch" href="/assets/js/611.bb8058c9.js"><link rel="prefetch" href="/assets/js/612.b0788f43.js"><link rel="prefetch" href="/assets/js/613.49511e65.js"><link rel="prefetch" href="/assets/js/614.fae3eacf.js"><link rel="prefetch" href="/assets/js/615.359e5899.js"><link rel="prefetch" href="/assets/js/616.905132f4.js"><link rel="prefetch" href="/assets/js/617.6c8c594e.js"><link rel="prefetch" href="/assets/js/618.6ac4f3ff.js"><link rel="prefetch" href="/assets/js/619.411a0c14.js"><link rel="prefetch" href="/assets/js/62.9aeede84.js"><link rel="prefetch" href="/assets/js/620.56155724.js"><link rel="prefetch" href="/assets/js/621.149d7933.js"><link rel="prefetch" href="/assets/js/622.f0c9bc08.js"><link rel="prefetch" href="/assets/js/623.e0989fbe.js"><link rel="prefetch" href="/assets/js/624.80b1f6a9.js"><link rel="prefetch" href="/assets/js/625.a0a392c4.js"><link rel="prefetch" href="/assets/js/626.56fd4e8a.js"><link rel="prefetch" href="/assets/js/627.264e7313.js"><link rel="prefetch" href="/assets/js/628.7a868b19.js"><link rel="prefetch" href="/assets/js/629.e7e15da1.js"><link rel="prefetch" href="/assets/js/63.4df55c8b.js"><link rel="prefetch" href="/assets/js/630.998c9b61.js"><link rel="prefetch" href="/assets/js/631.69aa049c.js"><link rel="prefetch" href="/assets/js/632.da3882c1.js"><link rel="prefetch" href="/assets/js/633.0d49adf0.js"><link rel="prefetch" href="/assets/js/634.f9984ede.js"><link rel="prefetch" href="/assets/js/635.f0439f65.js"><link rel="prefetch" href="/assets/js/636.54de4194.js"><link rel="prefetch" href="/assets/js/637.55d1c226.js"><link rel="prefetch" href="/assets/js/638.2a9a510f.js"><link rel="prefetch" href="/assets/js/639.8bec360e.js"><link rel="prefetch" href="/assets/js/64.7f3e5e81.js"><link rel="prefetch" href="/assets/js/640.30d7f96e.js"><link rel="prefetch" href="/assets/js/641.68100098.js"><link rel="prefetch" href="/assets/js/642.4b793817.js"><link rel="prefetch" href="/assets/js/643.d86a81ea.js"><link rel="prefetch" href="/assets/js/644.76327ea2.js"><link rel="prefetch" href="/assets/js/645.0ac6a923.js"><link rel="prefetch" href="/assets/js/646.cd92f728.js"><link rel="prefetch" href="/assets/js/647.f2c624e1.js"><link rel="prefetch" href="/assets/js/648.c8f3b955.js"><link rel="prefetch" href="/assets/js/649.6370753b.js"><link rel="prefetch" href="/assets/js/65.2beeae9b.js"><link rel="prefetch" href="/assets/js/650.afe31909.js"><link rel="prefetch" href="/assets/js/651.87971ec0.js"><link rel="prefetch" href="/assets/js/652.0adf10a6.js"><link rel="prefetch" href="/assets/js/653.1f655726.js"><link rel="prefetch" href="/assets/js/654.53e24c7c.js"><link rel="prefetch" href="/assets/js/655.c95a66ea.js"><link rel="prefetch" href="/assets/js/656.38b5a5ea.js"><link rel="prefetch" href="/assets/js/657.3167aa94.js"><link rel="prefetch" href="/assets/js/658.7c40ff62.js"><link rel="prefetch" href="/assets/js/659.5d2b9b54.js"><link rel="prefetch" href="/assets/js/66.44b214db.js"><link rel="prefetch" href="/assets/js/660.96a8da9e.js"><link rel="prefetch" href="/assets/js/661.4de3b6c1.js"><link rel="prefetch" href="/assets/js/662.7d9bf181.js"><link rel="prefetch" href="/assets/js/663.4ccaf40a.js"><link rel="prefetch" href="/assets/js/664.7ab4fa53.js"><link rel="prefetch" href="/assets/js/665.32245d26.js"><link rel="prefetch" href="/assets/js/666.e6617151.js"><link rel="prefetch" href="/assets/js/667.fb3b0547.js"><link rel="prefetch" href="/assets/js/668.3d1b2e36.js"><link rel="prefetch" href="/assets/js/669.b769905f.js"><link rel="prefetch" href="/assets/js/67.c31aaacf.js"><link rel="prefetch" href="/assets/js/670.88ed3af3.js"><link rel="prefetch" href="/assets/js/671.1aff6bfe.js"><link rel="prefetch" href="/assets/js/672.c90888f7.js"><link rel="prefetch" href="/assets/js/673.81241fdc.js"><link rel="prefetch" href="/assets/js/674.838a424d.js"><link rel="prefetch" href="/assets/js/675.603ac896.js"><link rel="prefetch" href="/assets/js/676.ff44b5dc.js"><link rel="prefetch" href="/assets/js/677.41a8087a.js"><link rel="prefetch" href="/assets/js/678.f0eb8d04.js"><link rel="prefetch" href="/assets/js/679.db78199e.js"><link rel="prefetch" href="/assets/js/68.08607c89.js"><link rel="prefetch" href="/assets/js/680.8fded9d4.js"><link rel="prefetch" href="/assets/js/681.0b019dfd.js"><link rel="prefetch" href="/assets/js/682.746e9190.js"><link rel="prefetch" href="/assets/js/683.d5c00845.js"><link rel="prefetch" href="/assets/js/684.b3207cad.js"><link rel="prefetch" href="/assets/js/685.d78f18ba.js"><link rel="prefetch" href="/assets/js/686.5aaecd19.js"><link rel="prefetch" href="/assets/js/687.821a06f4.js"><link rel="prefetch" href="/assets/js/688.8bbc1890.js"><link rel="prefetch" href="/assets/js/689.79b49e8c.js"><link rel="prefetch" href="/assets/js/69.509245d6.js"><link rel="prefetch" href="/assets/js/690.fa62f9dd.js"><link rel="prefetch" href="/assets/js/691.d3eb1a60.js"><link rel="prefetch" href="/assets/js/692.738f14bb.js"><link rel="prefetch" href="/assets/js/693.e6a497a2.js"><link rel="prefetch" href="/assets/js/694.6eea1c54.js"><link rel="prefetch" href="/assets/js/695.88e6acee.js"><link rel="prefetch" href="/assets/js/696.de10297f.js"><link rel="prefetch" href="/assets/js/697.96d04062.js"><link rel="prefetch" href="/assets/js/698.200cc84f.js"><link rel="prefetch" href="/assets/js/699.f11fc627.js"><link rel="prefetch" href="/assets/js/7.c16198b8.js"><link rel="prefetch" href="/assets/js/70.5f6285b1.js"><link rel="prefetch" href="/assets/js/700.93d48f59.js"><link rel="prefetch" href="/assets/js/701.b861f29a.js"><link rel="prefetch" href="/assets/js/702.dc82e05c.js"><link rel="prefetch" href="/assets/js/703.625ce87c.js"><link rel="prefetch" href="/assets/js/704.005a0a7c.js"><link rel="prefetch" href="/assets/js/705.16ad9230.js"><link rel="prefetch" href="/assets/js/706.4eead30a.js"><link rel="prefetch" href="/assets/js/707.730306ce.js"><link rel="prefetch" href="/assets/js/708.e37e743a.js"><link rel="prefetch" href="/assets/js/709.f2144f52.js"><link rel="prefetch" href="/assets/js/71.d5d0dae5.js"><link rel="prefetch" href="/assets/js/710.ee9d9b17.js"><link rel="prefetch" href="/assets/js/711.2813c338.js"><link rel="prefetch" href="/assets/js/712.e801bce4.js"><link rel="prefetch" href="/assets/js/713.21fc267c.js"><link rel="prefetch" href="/assets/js/714.4ec35525.js"><link rel="prefetch" href="/assets/js/715.fdc3cc84.js"><link rel="prefetch" href="/assets/js/716.324932ad.js"><link rel="prefetch" href="/assets/js/717.a072a66f.js"><link rel="prefetch" href="/assets/js/718.575e0305.js"><link rel="prefetch" href="/assets/js/719.5fdd55de.js"><link rel="prefetch" href="/assets/js/72.25648231.js"><link rel="prefetch" href="/assets/js/720.61477f23.js"><link rel="prefetch" href="/assets/js/721.c8bdab00.js"><link rel="prefetch" href="/assets/js/722.9255f385.js"><link rel="prefetch" href="/assets/js/723.f27792a4.js"><link rel="prefetch" href="/assets/js/724.cd504f88.js"><link rel="prefetch" href="/assets/js/725.55c845fa.js"><link rel="prefetch" href="/assets/js/726.bb49a9db.js"><link rel="prefetch" href="/assets/js/727.53d587fe.js"><link rel="prefetch" href="/assets/js/728.86972387.js"><link rel="prefetch" href="/assets/js/729.02fa0263.js"><link rel="prefetch" href="/assets/js/73.29902c1f.js"><link rel="prefetch" href="/assets/js/730.817156d1.js"><link rel="prefetch" href="/assets/js/731.6f5d6735.js"><link rel="prefetch" href="/assets/js/732.9a5ff781.js"><link rel="prefetch" href="/assets/js/733.bcb13916.js"><link rel="prefetch" href="/assets/js/734.11482b19.js"><link rel="prefetch" href="/assets/js/735.8c0d62f3.js"><link rel="prefetch" href="/assets/js/736.06465705.js"><link rel="prefetch" href="/assets/js/737.79741d87.js"><link rel="prefetch" href="/assets/js/738.cad12ff9.js"><link rel="prefetch" href="/assets/js/739.cb09e552.js"><link rel="prefetch" href="/assets/js/74.bcf666a0.js"><link rel="prefetch" href="/assets/js/740.029b1950.js"><link rel="prefetch" href="/assets/js/741.b937216a.js"><link rel="prefetch" href="/assets/js/742.ec02c706.js"><link rel="prefetch" href="/assets/js/743.70a96283.js"><link rel="prefetch" href="/assets/js/744.909b870c.js"><link rel="prefetch" href="/assets/js/745.80defb2d.js"><link rel="prefetch" href="/assets/js/746.fb972248.js"><link rel="prefetch" href="/assets/js/747.1a7b52fd.js"><link rel="prefetch" href="/assets/js/748.036f5d16.js"><link rel="prefetch" href="/assets/js/749.bd9a413d.js"><link rel="prefetch" href="/assets/js/75.23c5a54d.js"><link rel="prefetch" href="/assets/js/750.3f1ae8f5.js"><link rel="prefetch" href="/assets/js/751.8897bc9f.js"><link rel="prefetch" href="/assets/js/752.9b387659.js"><link rel="prefetch" href="/assets/js/753.f411ce21.js"><link rel="prefetch" href="/assets/js/754.26def684.js"><link rel="prefetch" href="/assets/js/755.923aff62.js"><link rel="prefetch" href="/assets/js/756.9965743a.js"><link rel="prefetch" href="/assets/js/757.0c6bbbfd.js"><link rel="prefetch" href="/assets/js/758.a830f8b1.js"><link rel="prefetch" href="/assets/js/759.987cad77.js"><link rel="prefetch" href="/assets/js/76.f54f3d4f.js"><link rel="prefetch" href="/assets/js/760.9f2652a0.js"><link rel="prefetch" href="/assets/js/761.df01a0ee.js"><link rel="prefetch" href="/assets/js/762.e0c05a1a.js"><link rel="prefetch" href="/assets/js/763.da8a60bd.js"><link rel="prefetch" href="/assets/js/764.9f2a2830.js"><link rel="prefetch" href="/assets/js/765.44e61161.js"><link rel="prefetch" href="/assets/js/766.cd7da8c1.js"><link rel="prefetch" href="/assets/js/767.6ea1fea2.js"><link rel="prefetch" href="/assets/js/768.91529b8f.js"><link rel="prefetch" href="/assets/js/769.194d7a3e.js"><link rel="prefetch" href="/assets/js/77.3d43a163.js"><link rel="prefetch" href="/assets/js/770.227fd5b9.js"><link rel="prefetch" href="/assets/js/771.44d5e37e.js"><link rel="prefetch" href="/assets/js/772.234d9bf6.js"><link rel="prefetch" href="/assets/js/773.ff1dfb6a.js"><link rel="prefetch" href="/assets/js/774.d401364f.js"><link rel="prefetch" href="/assets/js/775.37a7cf41.js"><link rel="prefetch" href="/assets/js/776.0cd10853.js"><link rel="prefetch" href="/assets/js/777.599a3a48.js"><link rel="prefetch" href="/assets/js/778.eef27a95.js"><link rel="prefetch" href="/assets/js/779.29351199.js"><link rel="prefetch" href="/assets/js/78.fd0780ac.js"><link rel="prefetch" href="/assets/js/780.74caed94.js"><link rel="prefetch" href="/assets/js/781.ee0fa9b5.js"><link rel="prefetch" href="/assets/js/782.7cffed09.js"><link rel="prefetch" href="/assets/js/783.7f01f518.js"><link rel="prefetch" href="/assets/js/784.5f65e3d7.js"><link rel="prefetch" href="/assets/js/785.d7e13880.js"><link rel="prefetch" href="/assets/js/786.6110d12f.js"><link rel="prefetch" href="/assets/js/787.334a5cdd.js"><link rel="prefetch" href="/assets/js/788.f261bc71.js"><link rel="prefetch" href="/assets/js/789.b6d74f7d.js"><link rel="prefetch" href="/assets/js/79.7b5d6224.js"><link rel="prefetch" href="/assets/js/790.fa948ec4.js"><link rel="prefetch" href="/assets/js/791.22080013.js"><link rel="prefetch" href="/assets/js/792.31ce806c.js"><link rel="prefetch" href="/assets/js/793.9fd0c56f.js"><link rel="prefetch" href="/assets/js/794.44a8cd9c.js"><link rel="prefetch" href="/assets/js/795.9f8346e5.js"><link rel="prefetch" href="/assets/js/796.0de9c7a1.js"><link rel="prefetch" href="/assets/js/797.83ac32a6.js"><link rel="prefetch" href="/assets/js/798.393fc81d.js"><link rel="prefetch" href="/assets/js/799.c1fb3981.js"><link rel="prefetch" href="/assets/js/8.3275ed06.js"><link rel="prefetch" href="/assets/js/80.df3f0a1f.js"><link rel="prefetch" href="/assets/js/800.2832adeb.js"><link rel="prefetch" href="/assets/js/801.04c58fc4.js"><link rel="prefetch" href="/assets/js/81.3d90ef6b.js"><link rel="prefetch" href="/assets/js/82.2d42448d.js"><link rel="prefetch" href="/assets/js/83.900b4de2.js"><link rel="prefetch" href="/assets/js/84.ff570f67.js"><link rel="prefetch" href="/assets/js/85.e3b3af39.js"><link rel="prefetch" href="/assets/js/86.17b5aedc.js"><link rel="prefetch" href="/assets/js/87.f931d1d6.js"><link rel="prefetch" href="/assets/js/88.d55863cd.js"><link rel="prefetch" href="/assets/js/89.15a9a6d7.js"><link rel="prefetch" href="/assets/js/9.04948a9d.js"><link rel="prefetch" href="/assets/js/90.22696aa9.js"><link rel="prefetch" href="/assets/js/91.f1bd8a2e.js"><link rel="prefetch" href="/assets/js/92.85733094.js"><link rel="prefetch" href="/assets/js/93.59bacfd7.js"><link rel="prefetch" href="/assets/js/94.a5f9b7a0.js"><link rel="prefetch" href="/assets/js/95.be52d65a.js"><link rel="prefetch" href="/assets/js/96.0b76ba8e.js"><link rel="prefetch" href="/assets/js/97.28183cd4.js"><link rel="prefetch" href="/assets/js/98.2d22829c.js"><link rel="prefetch" href="/assets/js/99.ed602a20.js">
    <link rel="stylesheet" href="/assets/css/0.styles.a3df589e.css">
  </head>
  <body>
    <div id="app" data-server-rendered="true"><div class="theme-container"><header class="navbar"><div class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/" class="home-link router-link-active"><!----> <span class="site-name">AJ</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/" class="nav-link">主页</a></div><div class="nav-item"><a href="/op/" class="nav-link">Devops</a></div><div class="nav-item"><a href="/golang/" class="nav-link">Go</a></div><div class="nav-item"><a href="/go-block/" class="nav-link">区块链</a></div><div class="nav-item"><a href="/k8s/" class="nav-link">k8s</a></div><div class="nav-item"><a href="/about.html" class="nav-link">关于我</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="更多" class="dropdown-title"><span class="title">更多</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/go-learning/" class="nav-link">go-learning</a></li><li class="dropdown-item"><!----> <a href="/post/flutter-guide/" class="nav-link">flutter</a></li><li class="dropdown-item"><!----> <a href="/mysql/" class="nav-link router-link-active">mysql</a></li><li class="dropdown-item"><!----> <a href="/python/" class="nav-link">python</a></li></ul></div></div> <a href="https://github.com/ChinaArJun" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg></a></nav></div></header> <div class="sidebar-mask"></div> <aside class="sidebar"><nav class="nav-links"><div class="nav-item"><a href="/" class="nav-link">主页</a></div><div class="nav-item"><a href="/op/" class="nav-link">Devops</a></div><div class="nav-item"><a href="/golang/" class="nav-link">Go</a></div><div class="nav-item"><a href="/go-block/" class="nav-link">区块链</a></div><div class="nav-item"><a href="/k8s/" class="nav-link">k8s</a></div><div class="nav-item"><a href="/about.html" class="nav-link">关于我</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="更多" class="dropdown-title"><span class="title">更多</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/go-learning/" class="nav-link">go-learning</a></li><li class="dropdown-item"><!----> <a href="/post/flutter-guide/" class="nav-link">flutter</a></li><li class="dropdown-item"><!----> <a href="/mysql/" class="nav-link router-link-active">mysql</a></li><li class="dropdown-item"><!----> <a href="/python/" class="nav-link">python</a></li></ul></div></div> <a href="https://github.com/ChinaArJun" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg></a></nav> <div></div> <div style="padding-left:1.5rem;"><div></div></div> <ul class="sidebar-links"><li><a href="/mysql/" aria-current="page" class="sidebar-link">SQL 必知必会</a></li><li><a href="/mysql/%E4%BB%8E%E6%A0%B9%E5%84%BF%E4%B8%8A%E7%90%86%E8%A7%A3%20MySQL/" class="sidebar-link">从根儿上理解 MySQL</a></li><li><a href="/mysql/01.MySQL基本操作.html" class="sidebar-link">1、MySQL基本操作</a></li><li><a href="/mysql/02.列类型和数据完整性.html" class="sidebar-link">2、列类型和数据完整性</a></li><li><a href="/mysql/03.数据库设计与查询语句.html" class="sidebar-link">3、  数据库设计与查询语句</a></li><li><a href="/mysql/04.视图、事务、索引、函数、GO连接MySQL.html" class="active sidebar-link">4-视图、事务、索引、函数、GO连接MySQL</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/mysql/04.视图、事务、索引、函数、GO连接MySQL.html#_1-1-今日目标" class="sidebar-link">1.1  今日目标</a></li><li class="sidebar-sub-header"><a href="/mysql/04.视图、事务、索引、函数、GO连接MySQL.html#_1-2多表查询分类" class="sidebar-link">1.2	多表查询分类</a></li><li class="sidebar-sub-header"><a href="/mysql/04.视图、事务、索引、函数、GO连接MySQL.html#_1-3子查询" class="sidebar-link">1.3	子查询</a></li><li class="sidebar-sub-header"><a href="/mysql/04.视图、事务、索引、函数、GO连接MySQL.html#_1-4视图【view】" class="sidebar-link">1.4	视图【view】</a></li><li class="sidebar-sub-header"><a href="/mysql/04.视图、事务、索引、函数、GO连接MySQL.html#_1-5事务【transaction】" class="sidebar-link">1.5	事务【transaction】</a></li><li class="sidebar-sub-header"><a href="/mysql/04.视图、事务、索引、函数、GO连接MySQL.html#_1-6索引【index】" class="sidebar-link">1.6	索引【index】</a></li><li class="sidebar-sub-header"><a href="/mysql/04.视图、事务、索引、函数、GO连接MySQL.html#_1-7-函数" class="sidebar-link">1.7  函数</a></li><li class="sidebar-sub-header"><a href="/mysql/04.视图、事务、索引、函数、GO连接MySQL.html#_1-8-预处理" class="sidebar-link">1.8  预处理</a></li><li class="sidebar-sub-header"><a href="/mysql/04.视图、事务、索引、函数、GO连接MySQL.html#_1-9存储过程【procedure】" class="sidebar-link">1.9	存储过程【procedure】</a></li><li class="sidebar-sub-header"><a href="/mysql/04.视图、事务、索引、函数、GO连接MySQL.html#_1-10-go连接mysql" class="sidebar-link">1.10 GO连接MySQL</a></li><li class="sidebar-sub-header"><a href="/mysql/04.视图、事务、索引、函数、GO连接MySQL.html#_1-11-小练习" class="sidebar-link">1.11  小练习</a></li></ul></li><li><a href="/mysql/MySQL高级知识-第1章.html" class="sidebar-link">MySQL高级知识-第1章</a></li><li><a href="/mysql/MySQL高级知识-第2章.html" class="sidebar-link">MySQL高级知识-第2章</a></li><li><a href="/mysql/MySQL高级知识-第3章.html" class="sidebar-link">MySQL高级知识-第3章</a></li><li><a href="/mysql/MySQL高级知识-第4章.html" class="sidebar-link">MySQL高级知识-第4章</a></li></ul> </aside> <!----> <!----> <main class="page"><div class="theme-default-content" style="margin-bottom:-5rem;"><div class="bar"><div class="bar-intro"><div class="text">
      流逝的是岁月，不变的是情怀.
        </div> <div class="text">
      坚持学习，是为了成就更好的自己. <br></div> <div>公众号[中关村程序员]</div></div></div> <!----></div> <div class="theme-default-content content__default"><h2 id="_1-1-今日目标"><a href="#_1-1-今日目标" class="header-anchor">#</a> 1.1  今日目标</h2> <ol><li>理解多表查询</li> <li>理解子查询</li> <li>能够创建视图</li> <li>能够删除视图</li> <li>能够查看创建视图的SQL语句</li> <li>能够理解事务的作用</li> <li>能够操作事务</li> <li>理解索引的作用</li> <li>能够创建索引</li> <li>能够删除索引</li> <li>知道常用的函数</li> <li>了解预处理语句的作用</li> <li>能够使用预处理语句</li> <li>了解存储过程的作用</li> <li>能够创建存储过程</li> <li>能够调用存储过程</li> <li>能够通过Go语言连接MySQL数据库</li> <li>能够通过Go语言对MySQL进行增、删、改、查的操作</li></ol> <h2 id="_1-2多表查询分类"><a href="#_1-2多表查询分类" class="header-anchor">#</a> 1.2	多表查询分类</h2> <p>将多个表的数据横向的联合起来。
1、	内连接
2、	外连接
a)	左外连接
b)	右外连接
3、	交叉连接
4、	自然连接</p> <h4 id="_1-2-1内连接【inner-join】"><a href="#_1-2-1内连接【inner-join】" class="header-anchor">#</a> 1.2.1	内连接【inner join】</h4> <div class="language-mysql extra-class"><pre class="language-text"><code>语法一：select 列名 from 表1 inner join 表2 on 表1.公共字段=表2.公共字段

语法二：select 列名 from 表1,表2 where 表1.公共字段=表2.公共字段
</code></pre></div><p>例题</p> <div class="language-sql extra-class"><pre class="language-sql"><code>方法一：
mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> stuname<span class="token punctuation">,</span>stusex<span class="token punctuation">,</span>writtenexam<span class="token punctuation">,</span>labexam <span class="token keyword">from</span> stuinfo <span class="token keyword">inner</span> <span class="token keyword">join</span> stumarks <span class="token keyword">on</span> stuinfo<span class="token punctuation">.</span>stuno<span class="token operator">=</span>stumarks<span class="token punctuation">.</span>stuno<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">----------+--------+-------------+---------+</span>
<span class="token operator">|</span> stuname  <span class="token operator">|</span> stusex <span class="token operator">|</span> writtenexam <span class="token operator">|</span> labexam <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------+--------+-------------+---------+</span>
<span class="token operator">|</span> 李斯文        <span class="token operator">|</span> 女      <span class="token operator">|</span>          <span class="token number">80</span> <span class="token operator">|</span>      <span class="token number">58</span> <span class="token operator">|</span>
<span class="token operator">|</span> 李文才        <span class="token operator">|</span> 男       <span class="token operator">|</span>          <span class="token number">50</span> <span class="token operator">|</span>      <span class="token number">90</span> <span class="token operator">|</span>
<span class="token operator">|</span> 欧阳俊雄        <span class="token operator">|</span> 男       <span class="token operator">|</span>          <span class="token number">65</span> <span class="token operator">|</span>      <span class="token number">50</span> <span class="token operator">|</span>
<span class="token operator">|</span> 张秋丽         <span class="token operator">|</span> 男       <span class="token operator">|</span>          <span class="token number">77</span> <span class="token operator">|</span>      <span class="token number">82</span> <span class="token operator">|</span>
<span class="token operator">|</span> 争青小子        <span class="token operator">|</span> 男       <span class="token operator">|</span>          <span class="token number">56</span> <span class="token operator">|</span>      <span class="token number">48</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------+--------+-------------+---------+</span>

方法二：
mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> stuinfo<span class="token punctuation">.</span>stuno<span class="token punctuation">,</span>stuname<span class="token punctuation">,</span>stusex<span class="token punctuation">,</span>writtenexam<span class="token punctuation">,</span>labexam <span class="token keyword">from</span> stuinfo<span class="token punctuation">,</span>stumarks <span class="token keyword">where</span> stuinfo<span class="token punctuation">.</span>stuno<span class="token operator">=</span>stumarks<span class="token punctuation">.</span>stuno<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+-------------+---------+</span>
<span class="token operator">|</span> stuno  <span class="token operator">|</span> stuname  <span class="token operator">|</span> stusex <span class="token operator">|</span> writtenexam <span class="token operator">|</span> labexam <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+-------------+---------+</span>
<span class="token operator">|</span> s25303 <span class="token operator">|</span> 李斯文        <span class="token operator">|</span> 女      <span class="token operator">|</span>          <span class="token number">80</span> <span class="token operator">|</span>      <span class="token number">58</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25302 <span class="token operator">|</span> 李文才        <span class="token operator">|</span> 男       <span class="token operator">|</span>          <span class="token number">50</span> <span class="token operator">|</span>      <span class="token number">90</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25304 <span class="token operator">|</span> 欧阳俊雄        <span class="token operator">|</span> 男       <span class="token operator">|</span>          <span class="token number">65</span> <span class="token operator">|</span>      <span class="token number">50</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25301 <span class="token operator">|</span> 张秋丽         <span class="token operator">|</span> 男       <span class="token operator">|</span>          <span class="token number">77</span> <span class="token operator">|</span>      <span class="token number">82</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25318 <span class="token operator">|</span> 争青小子        <span class="token operator">|</span> 男       <span class="token operator">|</span>          <span class="token number">56</span> <span class="token operator">|</span>      <span class="token number">48</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+-------------+---------+</span>

可以给表取别名
mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> i<span class="token punctuation">.</span>stuno<span class="token punctuation">,</span>stuname<span class="token punctuation">,</span>stusex<span class="token punctuation">,</span>writtenexam<span class="token punctuation">,</span>labexam <span class="token keyword">from</span> stuinfo i<span class="token punctuation">,</span>stumarks s <span class="token keyword">where</span> i<span class="token punctuation">.</span>stuno<span class="token operator">=</span>s<span class="token punctuation">.</span>stuno<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+-------------+---------+</span>
<span class="token operator">|</span> stuno  <span class="token operator">|</span> stuname  <span class="token operator">|</span> stusex <span class="token operator">|</span> writtenexam <span class="token operator">|</span> labexam <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+-------------+---------+</span>
<span class="token operator">|</span> s25303 <span class="token operator">|</span> 李斯文        <span class="token operator">|</span> 女      <span class="token operator">|</span>          <span class="token number">80</span> <span class="token operator">|</span>      <span class="token number">58</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25302 <span class="token operator">|</span> 李文才        <span class="token operator">|</span> 男       <span class="token operator">|</span>          <span class="token number">50</span> <span class="token operator">|</span>      <span class="token number">90</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25304 <span class="token operator">|</span> 欧阳俊雄        <span class="token operator">|</span> 男       <span class="token operator">|</span>          <span class="token number">65</span> <span class="token operator">|</span>      <span class="token number">50</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25301 <span class="token operator">|</span> 张秋丽         <span class="token operator">|</span> 男       <span class="token operator">|</span>          <span class="token number">77</span> <span class="token operator">|</span>      <span class="token number">82</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25318 <span class="token operator">|</span> 争青小子        <span class="token operator">|</span> 男       <span class="token operator">|</span>          <span class="token number">56</span> <span class="token operator">|</span>      <span class="token number">48</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+-------------+---------+</span>
<span class="token number">5</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><p>脚下留心：显示公共字段需要指定表名 <img src="http://static.zhequtao.com/study/mysql/day4/1537061664763.png" alt="1537061664763"></p> <p><img src="http://static.zhequtao.com/study/mysql/day4/1537061715978.png" alt="1537061715978"></p> <div class="language-sql extra-class"><pre class="language-sql"><code>思考：
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表<span class="token number">1</span> <span class="token keyword">inner</span> <span class="token keyword">join</span> 表<span class="token number">2</span> <span class="token keyword">on</span> 表<span class="token number">1.</span>公共字段<span class="token operator">=</span>表<span class="token number">2.</span>公共字段  和
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表<span class="token number">2</span> <span class="token keyword">inner</span> <span class="token keyword">join</span> 表<span class="token number">1</span> <span class="token keyword">on</span> 表<span class="token number">1.</span>公共字段<span class="token operator">=</span>表<span class="token number">2.</span>公共字段   结果是否一样？
答：一样的，因为内连接获取的是两个表的公共部分
</code></pre></div><div class="language-sql extra-class"><pre class="language-sql"><code>多学一招：三个表的内连接如何实现？
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表<span class="token number">1</span> <span class="token keyword">inner</span> <span class="token keyword">join</span> 表<span class="token number">2</span> <span class="token keyword">on</span> 表<span class="token number">1.</span>公共字段<span class="token operator">=</span>表<span class="token number">2.</span>公共字段
<span class="token keyword">inner</span> <span class="token keyword">join</span> 表<span class="token number">3</span> <span class="token keyword">on</span> 表<span class="token number">2.</span>公共字段<span class="token operator">=</span>表<span class="token number">3.</span>公共字段
</code></pre></div><h4 id="_1-2-2左外连接【left-join】"><a href="#_1-2-2左外连接【left-join】" class="header-anchor">#</a> 1.2.2	左外连接【left join】</h4> <p>以左边的表为标准，如果右边的表没有对应的记录，用NULL填充。</p> <div class="language-sql extra-class"><pre class="language-sql"><code>语法：<span class="token keyword">select</span> 列名 <span class="token keyword">from</span> 表<span class="token number">1</span> <span class="token keyword">left</span> <span class="token keyword">join</span> 表<span class="token number">2</span> <span class="token keyword">on</span> 表<span class="token number">1.</span>公共字段<span class="token operator">=</span>表<span class="token number">2.</span>公共字段
</code></pre></div><p>例题</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> stuname<span class="token punctuation">,</span>writtenexam<span class="token punctuation">,</span>labexam <span class="token keyword">from</span> stuinfo <span class="token keyword">left</span> <span class="token keyword">join</span> stumarks <span class="token keyword">on</span> stuinfo<span class="token punctuation">.</span>stuno<span class="token operator">=</span>stumarks<span class="token punctuation">.</span>stuno<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">----------+-------------+---------+</span>
<span class="token operator">|</span> stuname  <span class="token operator">|</span> writtenexam <span class="token operator">|</span> labexam <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------+-------------+---------+</span>
<span class="token operator">|</span> 张秋丽         <span class="token operator">|</span>          <span class="token number">77</span> <span class="token operator">|</span>      <span class="token number">82</span> <span class="token operator">|</span>
<span class="token operator">|</span> 李文才        <span class="token operator">|</span>          <span class="token number">50</span> <span class="token operator">|</span>      <span class="token number">90</span> <span class="token operator">|</span>
<span class="token operator">|</span> 李斯文        <span class="token operator">|</span>          <span class="token number">80</span> <span class="token operator">|</span>      <span class="token number">58</span> <span class="token operator">|</span>
<span class="token operator">|</span> 欧阳俊雄        <span class="token operator">|</span>          <span class="token number">65</span> <span class="token operator">|</span>      <span class="token number">50</span> <span class="token operator">|</span>
<span class="token operator">|</span> 诸葛丽丽         <span class="token operator">|</span>        <span class="token boolean">NULL</span> <span class="token operator">|</span>    <span class="token boolean">NULL</span> <span class="token operator">|</span>
<span class="token operator">|</span> 争青小子        <span class="token operator">|</span>          <span class="token number">56</span> <span class="token operator">|</span>      <span class="token number">48</span> <span class="token operator">|</span>
<span class="token operator">|</span> 梅超风        <span class="token operator">|</span>        <span class="token boolean">NULL</span> <span class="token operator">|</span>    <span class="token boolean">NULL</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------+-------------+---------+</span>
</code></pre></div><div class="language-sql extra-class"><pre class="language-sql"><code>思考：
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表<span class="token number">1</span> <span class="token keyword">left</span> <span class="token keyword">join</span> 表<span class="token number">2</span> <span class="token keyword">on</span> 表<span class="token number">1.</span>公共字段<span class="token operator">=</span>表<span class="token number">2.</span>公共字段
和
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表<span class="token number">2</span> <span class="token keyword">left</span> <span class="token keyword">join</span> 表<span class="token number">1</span> <span class="token keyword">on</span> 表<span class="token number">1.</span>公共字段<span class="token operator">=</span>表<span class="token number">2.</span>公共字段   是否一样？
答：不一样，左连接一左边的表为准。
</code></pre></div><h4 id="_1-2-3右外连接【right-join】"><a href="#_1-2-3右外连接【right-join】" class="header-anchor">#</a> 1.2.3	右外连接【right join】</h4> <p>以右边的表为标准，如果左边的表没有对应的记录，用NULL填充。</p> <div class="language-sql extra-class"><pre class="language-sql"><code>语法：<span class="token keyword">select</span> 列名 <span class="token keyword">from</span> 表<span class="token number">1</span> <span class="token keyword">right</span> <span class="token keyword">join</span> 表<span class="token number">2</span> <span class="token keyword">on</span> 表<span class="token number">1.</span>公共字段<span class="token operator">=</span>表<span class="token number">2.</span>公共字段
</code></pre></div><p>例题</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> stuname<span class="token punctuation">,</span>writtenexam<span class="token punctuation">,</span>labexam <span class="token keyword">from</span> stuinfo <span class="token keyword">right</span> <span class="token keyword">join</span> stumarks <span class="token keyword">on</span> stuinfo<span class="token punctuation">.</span>stuno<span class="token operator">=</span>stumarks<span class="token punctuation">.</span>stuno<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">----------+-------------+---------+</span>
<span class="token operator">|</span> stuname  <span class="token operator">|</span> writtenexam <span class="token operator">|</span> labexam <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------+-------------+---------+</span>
<span class="token operator">|</span> 李斯文        <span class="token operator">|</span>          <span class="token number">80</span> <span class="token operator">|</span>      <span class="token number">58</span> <span class="token operator">|</span>
<span class="token operator">|</span> 李文才        <span class="token operator">|</span>          <span class="token number">50</span> <span class="token operator">|</span>      <span class="token number">90</span> <span class="token operator">|</span>
<span class="token operator">|</span> 欧阳俊雄        <span class="token operator">|</span>          <span class="token number">65</span> <span class="token operator">|</span>      <span class="token number">50</span> <span class="token operator">|</span>
<span class="token operator">|</span> 张秋丽         <span class="token operator">|</span>          <span class="token number">77</span> <span class="token operator">|</span>      <span class="token number">82</span> <span class="token operator">|</span>
<span class="token operator">|</span> 争青小子        <span class="token operator">|</span>          <span class="token number">56</span> <span class="token operator">|</span>      <span class="token number">48</span> <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token boolean">NULL</span>     <span class="token operator">|</span>          <span class="token number">66</span> <span class="token operator">|</span>      <span class="token number">77</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------+-------------+---------+</span>
<span class="token number">6</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><div class="language-sql extra-class"><pre class="language-sql"><code>思考：
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表<span class="token number">1</span> <span class="token keyword">left</span> <span class="token keyword">join</span> 表<span class="token number">2</span> <span class="token keyword">on</span> 表<span class="token number">1.</span>公共字段<span class="token operator">=</span>表<span class="token number">2.</span>公共字段
和
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表<span class="token number">2</span> <span class="token keyword">right</span> <span class="token keyword">join</span> 表<span class="token number">1</span> <span class="token keyword">on</span> 表<span class="token number">1.</span>公共字段<span class="token operator">=</span>表<span class="token number">2.</span>公共字段  是否一样？

答：一样的
</code></pre></div><h4 id="_1-2-4交叉连接【cross-join】"><a href="#_1-2-4交叉连接【cross-join】" class="header-anchor">#</a> 1.2.4	交叉连接【cross join】</h4> <p>插入测试数据</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">create</span> <span class="token keyword">table</span> t1<span class="token punctuation">(</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> id <span class="token keyword">int</span><span class="token punctuation">,</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> name <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token punctuation">)</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.06</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">insert</span> <span class="token keyword">into</span> t1 <span class="token keyword">values</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'berry'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">2</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">create</span> <span class="token keyword">table</span> t2<span class="token punctuation">(</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> id <span class="token keyword">int</span><span class="token punctuation">,</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> score <span class="token keyword">int</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.02</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">insert</span> <span class="token keyword">into</span> t2 <span class="token keyword">values</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">88</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">99</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre></div><p>1、如果没有连接表达式返回的是笛卡尔积</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> t1 <span class="token keyword">cross</span> <span class="token keyword">join</span> t2<span class="token punctuation">;</span>   <span class="token comment"># 返回笛卡尔积</span>
<span class="token operator">+</span><span class="token comment">------+-------+------+-------+</span>
<span class="token operator">|</span> id   <span class="token operator">|</span> name  <span class="token operator">|</span> id   <span class="token operator">|</span> score <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+-------+------+-------+</span>
<span class="token operator">|</span>    <span class="token number">1</span> <span class="token operator">|</span> tom   <span class="token operator">|</span>    <span class="token number">1</span> <span class="token operator">|</span>    <span class="token number">88</span> <span class="token operator">|</span>
<span class="token operator">|</span>    <span class="token number">2</span> <span class="token operator">|</span> berry <span class="token operator">|</span>    <span class="token number">1</span> <span class="token operator">|</span>    <span class="token number">88</span> <span class="token operator">|</span>
<span class="token operator">|</span>    <span class="token number">1</span> <span class="token operator">|</span> tom   <span class="token operator">|</span>    <span class="token number">2</span> <span class="token operator">|</span>    <span class="token number">99</span> <span class="token operator">|</span>
<span class="token operator">|</span>    <span class="token number">2</span> <span class="token operator">|</span> berry <span class="token operator">|</span>    <span class="token number">2</span> <span class="token operator">|</span>    <span class="token number">99</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+-------+------+-------+</span>
</code></pre></div><p>2、如果有连接表达式等价于内连接</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> t1 <span class="token keyword">cross</span> <span class="token keyword">join</span> t2 <span class="token keyword">where</span> t1<span class="token punctuation">.</span>id<span class="token operator">=</span>t2<span class="token punctuation">.</span>id<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">------+-------+------+-------+</span>
<span class="token operator">|</span> id   <span class="token operator">|</span> name  <span class="token operator">|</span> id   <span class="token operator">|</span> score <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+-------+------+-------+</span>
<span class="token operator">|</span>    <span class="token number">1</span> <span class="token operator">|</span> tom   <span class="token operator">|</span>    <span class="token number">1</span> <span class="token operator">|</span>    <span class="token number">88</span> <span class="token operator">|</span>
<span class="token operator">|</span>    <span class="token number">2</span> <span class="token operator">|</span> berry <span class="token operator">|</span>    <span class="token number">2</span> <span class="token operator">|</span>    <span class="token number">99</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+-------+------+-------+</span>
</code></pre></div><h4 id="_1-2-5自然连接【natural】"><a href="#_1-2-5自然连接【natural】" class="header-anchor">#</a> 1.2.5	自然连接【natural】</h4> <div class="language- extra-class"><pre class="language-text"><code>自动的判断连接条件，它是过同名字段来判断的
</code></pre></div><p>自然连接又分为：</p> <ol><li>自然内连接		natural join</li> <li>自然左外连接		natural left join</li> <li>自然右外连接		natural right join</li></ol> <p>例题：</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment"># 自然内连接</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stuinfo <span class="token keyword">natural</span> <span class="token keyword">join</span> stumarks<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+---------+-------------+---------+</span>
<span class="token operator">|</span> stuNo  <span class="token operator">|</span> stuName  <span class="token operator">|</span> stuSex <span class="token operator">|</span> stuAge <span class="token operator">|</span> stuSeat <span class="token operator">|</span> stuAddress <span class="token operator">|</span> examNo  <span class="token operator">|</span> writtenExam <span class="token operator">|</span> labExam <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+---------+-------------+---------+</span>
<span class="token operator">|</span> s25303 <span class="token operator">|</span> 李斯文        <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">22</span> <span class="token operator">|</span>       <span class="token number">2</span> <span class="token operator">|</span> 北京           <span class="token operator">|</span> s271811 <span class="token operator">|</span>          <span class="token number">80</span> <span class="token operator">|</span>
  <span class="token number">58</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25302 <span class="token operator">|</span> 李文才        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">31</span> <span class="token operator">|</span>       <span class="token number">3</span> <span class="token operator">|</span> 上海          <span class="token operator">|</span> s271813 <span class="token operator">|</span>          <span class="token number">50</span> <span class="token operator">|</span>
  <span class="token number">90</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25304 <span class="token operator">|</span> 欧阳俊雄        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">28</span> <span class="token operator">|</span>       <span class="token number">4</span> <span class="token operator">|</span> 天津           <span class="token operator">|</span> s271815 <span class="token operator">|</span>          <span class="token number">65</span> <span class="token operator">|</span>
     <span class="token number">50</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25301 <span class="token operator">|</span> 张秋丽         <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">18</span> <span class="token operator">|</span>       <span class="token number">1</span> <span class="token operator">|</span> 北京           <span class="token operator">|</span> s271816 <span class="token operator">|</span>          <span class="token number">77</span> <span class="token operator">|</span>
    <span class="token number">82</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25318 <span class="token operator">|</span> 争青小子        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">26</span> <span class="token operator">|</span>       <span class="token number">6</span> <span class="token operator">|</span> 天津           <span class="token operator">|</span> s271819 <span class="token operator">|</span>          <span class="token number">56</span> <span class="token operator">|</span>
     <span class="token number">48</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+---------+-------------+---------+</span>
<span class="token number">5</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

<span class="token comment"># 自然左外连接</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stuinfo <span class="token keyword">natural</span> <span class="token keyword">left</span> <span class="token keyword">join</span> stumarks<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+---------+-------------+---------+</span>
<span class="token operator">|</span> stuNo  <span class="token operator">|</span> stuName  <span class="token operator">|</span> stuSex <span class="token operator">|</span> stuAge <span class="token operator">|</span> stuSeat <span class="token operator">|</span> stuAddress <span class="token operator">|</span> examNo  <span class="token operator">|</span> writtenExam <span class="token operator">|</span> labExam <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+---------+-------------+---------+</span>
<span class="token operator">|</span> s25301 <span class="token operator">|</span> 张秋丽         <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">18</span> <span class="token operator">|</span>       <span class="token number">1</span> <span class="token operator">|</span> 北京           <span class="token operator">|</span> s271816 <span class="token operator">|</span>          <span class="token number">77</span>
    <span class="token number">82</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25302 <span class="token operator">|</span> 李文才        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">31</span> <span class="token operator">|</span>       <span class="token number">3</span> <span class="token operator">|</span> 上海          <span class="token operator">|</span> s271813 <span class="token operator">|</span>          <span class="token number">50</span> <span class="token operator">|</span>
  <span class="token number">90</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25303 <span class="token operator">|</span> 李斯文        <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">22</span> <span class="token operator">|</span>       <span class="token number">2</span> <span class="token operator">|</span> 北京           <span class="token operator">|</span> s271811 <span class="token operator">|</span>          <span class="token number">80</span> <span class="token operator">|</span>
  <span class="token number">58</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25304 <span class="token operator">|</span> 欧阳俊雄        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">28</span> <span class="token operator">|</span>       <span class="token number">4</span> <span class="token operator">|</span> 天津           <span class="token operator">|</span> s271815 <span class="token operator">|</span>          <span class="token number">65</span>
     <span class="token number">50</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25305 <span class="token operator">|</span> 诸葛丽丽         <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">23</span> <span class="token operator">|</span>       <span class="token number">7</span> <span class="token operator">|</span> 河南           <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>        <span class="token boolean">NULL</span>
   <span class="token boolean">NULL</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25318 <span class="token operator">|</span> 争青小子        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">26</span> <span class="token operator">|</span>       <span class="token number">6</span> <span class="token operator">|</span> 天津           <span class="token operator">|</span> s271819 <span class="token operator">|</span>          <span class="token number">56</span>
     <span class="token number">48</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25319 <span class="token operator">|</span> 梅超风        <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">23</span> <span class="token operator">|</span>       <span class="token number">5</span> <span class="token operator">|</span> 河北          <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>        <span class="token boolean">NULL</span> <span class="token operator">|</span>
ULL <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+---------+-------------+---------+</span>
<span class="token number">7</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

<span class="token comment"># 自然右外连接</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stuinfo <span class="token keyword">natural</span> <span class="token keyword">right</span> <span class="token keyword">join</span> stumarks<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">--------+---------+-------------+---------+----------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> stuNo  <span class="token operator">|</span> examNo  <span class="token operator">|</span> writtenExam <span class="token operator">|</span> labExam <span class="token operator">|</span> stuName  <span class="token operator">|</span> stuSex <span class="token operator">|</span> stuAge <span class="token operator">|</span> stuSeat <span class="token operator">|</span> stuAddress <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+---------+-------------+---------+----------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> s25303 <span class="token operator">|</span> s271811 <span class="token operator">|</span>          <span class="token number">80</span> <span class="token operator">|</span>      <span class="token number">58</span> <span class="token operator">|</span> 李斯文        <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">22</span> <span class="token operator">|</span>       <span class="token number">2</span> <span class="token operator">|</span> 北京
     <span class="token operator">|</span>
<span class="token operator">|</span> s25302 <span class="token operator">|</span> s271813 <span class="token operator">|</span>          <span class="token number">50</span> <span class="token operator">|</span>      <span class="token number">90</span> <span class="token operator">|</span> 李文才        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">31</span> <span class="token operator">|</span>       <span class="token number">3</span> <span class="token operator">|</span> 上海
     <span class="token operator">|</span>
<span class="token operator">|</span> s25304 <span class="token operator">|</span> s271815 <span class="token operator">|</span>          <span class="token number">65</span> <span class="token operator">|</span>      <span class="token number">50</span> <span class="token operator">|</span> 欧阳俊雄        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">28</span> <span class="token operator">|</span>       <span class="token number">4</span> <span class="token operator">|</span> 天津
        <span class="token operator">|</span>
<span class="token operator">|</span> s25301 <span class="token operator">|</span> s271816 <span class="token operator">|</span>          <span class="token number">77</span> <span class="token operator">|</span>      <span class="token number">82</span> <span class="token operator">|</span> 张秋丽         <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">18</span> <span class="token operator">|</span>       <span class="token number">1</span> <span class="token operator">|</span> 北京
       <span class="token operator">|</span>
<span class="token operator">|</span> s25318 <span class="token operator">|</span> s271819 <span class="token operator">|</span>          <span class="token number">56</span> <span class="token operator">|</span>      <span class="token number">48</span> <span class="token operator">|</span> 争青小子        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">26</span> <span class="token operator">|</span>       <span class="token number">6</span> <span class="token operator">|</span> 天津
        <span class="token operator">|</span>
<span class="token operator">|</span> s25320 <span class="token operator">|</span> s271820 <span class="token operator">|</span>          <span class="token number">66</span> <span class="token operator">|</span>      <span class="token number">77</span> <span class="token operator">|</span> <span class="token boolean">NULL</span>     <span class="token operator">|</span> <span class="token boolean">NULL</span>   <span class="token operator">|</span>   <span class="token boolean">NULL</span> <span class="token operator">|</span>    <span class="token boolean">NULL</span> <span class="token operator">|</span> <span class="token boolean">NULL</span>       <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+---------+-------------+---------+----------+--------+--------+---------+------------+</span>
<span class="token number">6</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

</code></pre></div><p>自然连接结论：</p> <ol><li><p>表连接通过同名的字段来连接的</p></li> <li><p>如果没有同名的字段返回笛卡尔积</p></li> <li><p>会对结果进行整理，整理的规则如下</p> <p>a)	连接字段保留一个</p> <p>b)	连接字段放在最前面</p> <p>c)     左外连接左边在前，右外连接右表在前</p></li></ol> <h4 id="_1-2-6using"><a href="#_1-2-6using" class="header-anchor">#</a> 1.2.6	using()</h4> <ol><li><p>用来指定连接字段。</p></li> <li><p>using()也会对连接字段进行整理，整理方式和自然连接是一样的。</p></li></ol> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stuinfo <span class="token keyword">inner</span> <span class="token keyword">join</span> stumarks <span class="token keyword">using</span><span class="token punctuation">(</span>stuno<span class="token punctuation">)</span><span class="token punctuation">;</span>   <span class="token comment"># using指定字段</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+---------+-------------+---------+</span>
<span class="token operator">|</span> stuNo  <span class="token operator">|</span> stuName  <span class="token operator">|</span> stuSex <span class="token operator">|</span> stuAge <span class="token operator">|</span> stuSeat <span class="token operator">|</span> stuAddress <span class="token operator">|</span> examNo  <span class="token operator">|</span> writtenExam <span class="token operator">|</span> labExam <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+---------+-------------+---------+</span>
<span class="token operator">|</span> s25303 <span class="token operator">|</span> 李斯文        <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">22</span> <span class="token operator">|</span>       <span class="token number">2</span> <span class="token operator">|</span> 北京           <span class="token operator">|</span> s271811 <span class="token operator">|</span>          <span class="token number">80</span> <span class="token operator">|</span>
  <span class="token number">58</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25302 <span class="token operator">|</span> 李文才        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">31</span> <span class="token operator">|</span>       <span class="token number">3</span> <span class="token operator">|</span> 上海          <span class="token operator">|</span> s271813 <span class="token operator">|</span>          <span class="token number">50</span> <span class="token operator">|</span>
  <span class="token number">90</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25304 <span class="token operator">|</span> 欧阳俊雄        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">28</span> <span class="token operator">|</span>       <span class="token number">4</span> <span class="token operator">|</span> 天津           <span class="token operator">|</span> s271815 <span class="token operator">|</span>          <span class="token number">65</span> <span class="token operator">|</span>
     <span class="token number">50</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25301 <span class="token operator">|</span> 张秋丽         <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">18</span> <span class="token operator">|</span>       <span class="token number">1</span> <span class="token operator">|</span> 北京           <span class="token operator">|</span> s271816 <span class="token operator">|</span>          <span class="token number">77</span> <span class="token operator">|</span>
    <span class="token number">82</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25318 <span class="token operator">|</span> 争青小子        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">26</span> <span class="token operator">|</span>       <span class="token number">6</span> <span class="token operator">|</span> 天津           <span class="token operator">|</span> s271819 <span class="token operator">|</span>          <span class="token number">56</span> <span class="token operator">|</span>
     <span class="token number">48</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+---------+-------------+---------+</span>
<span class="token number">5</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><h2 id="_1-3子查询"><a href="#_1-3子查询" class="header-anchor">#</a> 1.3	子查询</h2> <p>语法</p> <div class="language-sql extra-class"><pre class="language-sql"><code>语法：<span class="token keyword">select</span> 语句 <span class="token keyword">where</span> 条件 <span class="token punctuation">(</span><span class="token keyword">select</span> … <span class="token keyword">from</span> 表<span class="token punctuation">)</span>
</code></pre></div><ol><li>外面的查询称为父查询，括号中的查询称为子查询</li> <li>子查询为父查询提供查询条件</li></ol> <h4 id="_1-3-1例题"><a href="#_1-3-1例题" class="header-anchor">#</a> 1.3.1	例题</h4> <p>1、查找笔试80分的学生</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stuinfo <span class="token keyword">where</span> stuno<span class="token operator">=</span><span class="token punctuation">(</span><span class="token keyword">select</span> stuno <span class="token keyword">from</span> stumarks <span class="token keyword">where</span> writtenexam<span class="token operator">=</span><span class="token number">80</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">--------+---------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> stuNo  <span class="token operator">|</span> stuName <span class="token operator">|</span> stuSex <span class="token operator">|</span> stuAge <span class="token operator">|</span> stuSeat <span class="token operator">|</span> stuAddress <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+---------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> s25303 <span class="token operator">|</span> 李斯文       <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">22</span> <span class="token operator">|</span>       <span class="token number">2</span> <span class="token operator">|</span> 北京           <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+---------+--------+--------+---------+------------+</span>
</code></pre></div><p>2、查找笔试最高分的学生</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment"># 方法一：</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stuinfo <span class="token keyword">where</span> stuno<span class="token operator">=</span><span class="token punctuation">(</span><span class="token keyword">select</span> stuno <span class="token keyword">from</span> stumarks <span class="token keyword">order</span> <span class="token keyword">by</span> writtenexam <span class="token keyword">desc</span> <span class="token keyword">limit</span>
 <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">--------+---------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> stuNo  <span class="token operator">|</span> stuName <span class="token operator">|</span> stuSex <span class="token operator">|</span> stuAge <span class="token operator">|</span> stuSeat <span class="token operator">|</span> stuAddress <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+---------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> s25303 <span class="token operator">|</span> 李斯文       <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">22</span> <span class="token operator">|</span>       <span class="token number">2</span> <span class="token operator">|</span> 北京           <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+---------+--------+--------+---------+------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

<span class="token comment"># 方法二：</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stuinfo <span class="token keyword">where</span> stuno<span class="token operator">=</span><span class="token punctuation">(</span><span class="token keyword">select</span> stuno <span class="token keyword">from</span> stumarks <span class="token keyword">where</span> writtenexam<span class="token operator">=</span><span class="token punctuation">(</span><span class="token keyword">select</span> <span class="token function">max</span><span class="token punctuation">(</span>writtenexam<span class="token punctuation">)</span> <span class="token keyword">from</span> stumarks<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">--------+---------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> stuNo  <span class="token operator">|</span> stuName <span class="token operator">|</span> stuSex <span class="token operator">|</span> stuAge <span class="token operator">|</span> stuSeat <span class="token operator">|</span> stuAddress <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+---------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> s25303 <span class="token operator">|</span> 李斯文       <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">22</span> <span class="token operator">|</span>       <span class="token number">2</span> <span class="token operator">|</span> 北京           <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+---------+--------+--------+---------+------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><div class="language- extra-class"><pre class="language-text"><code>脚下留心：上面的例题，子查询只能返回一个值。如果子查询返回多个值就不能用“=”了,需要用 in
</code></pre></div><h4 id="_1-3-2in-not-in子查询"><a href="#_1-3-2in-not-in子查询" class="header-anchor">#</a> 1.3.2	in|not in子查询</h4> <p>用于子查询的返回结果多个值。</p> <p>1、查找笔试成绩及格的同学</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stuinfo <span class="token keyword">where</span> stuno <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token keyword">select</span> stuno <span class="token keyword">from</span> stumarks <span class="token keyword">where</span> writtenexam<span class="token operator">&gt;=</span><span class="token number">60</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> stuNo  <span class="token operator">|</span> stuName  <span class="token operator">|</span> stuSex <span class="token operator">|</span> stuAge <span class="token operator">|</span> stuSeat <span class="token operator">|</span> stuAddress <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> s25301 <span class="token operator">|</span> 张秋丽         <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">18</span> <span class="token operator">|</span>       <span class="token number">1</span> <span class="token operator">|</span> 北京           <span class="token operator">|</span>
<span class="token operator">|</span> s25303 <span class="token operator">|</span> 李斯文        <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">22</span> <span class="token operator">|</span>       <span class="token number">2</span> <span class="token operator">|</span> 北京           <span class="token operator">|</span>
<span class="token operator">|</span> s25304 <span class="token operator">|</span> 欧阳俊雄        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">28</span> <span class="token operator">|</span>       <span class="token number">4</span> <span class="token operator">|</span> 天津           <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
<span class="token number">3</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><p>2、查询不及格的同学</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stuinfo <span class="token keyword">where</span> stuno  <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token keyword">select</span> stuno <span class="token keyword">from</span> stumarks <span class="token keyword">where</span> writtenexam<span class="token operator">&lt;=</span><span class="token number">60</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> stuNo  <span class="token operator">|</span> stuName  <span class="token operator">|</span> stuSex <span class="token operator">|</span> stuAge <span class="token operator">|</span> stuSeat <span class="token operator">|</span> stuAddress <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> s25302 <span class="token operator">|</span> 李文才        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">31</span> <span class="token operator">|</span>       <span class="token number">3</span> <span class="token operator">|</span> 上海          <span class="token operator">|</span>
<span class="token operator">|</span> s25318 <span class="token operator">|</span> 争青小子        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">26</span> <span class="token operator">|</span>       <span class="token number">6</span> <span class="token operator">|</span> 天津           <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
</code></pre></div><p>3、查询没有通过的同学（不及格，缺考）</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stuinfo <span class="token keyword">where</span> stuno  <span class="token operator">not</span> <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token keyword">select</span> stuno <span class="token keyword">from</span> stumarks <span class="token keyword">where</span> writtenexam<span class="token operator">&gt;=</span><span class="token number">60</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> stuNo  <span class="token operator">|</span> stuName  <span class="token operator">|</span> stuSex <span class="token operator">|</span> stuAge <span class="token operator">|</span> stuSeat <span class="token operator">|</span> stuAddress <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> s25302 <span class="token operator">|</span> 李文才        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">31</span> <span class="token operator">|</span>       <span class="token number">3</span> <span class="token operator">|</span> 上海          <span class="token operator">|</span>
<span class="token operator">|</span> s25305 <span class="token operator">|</span> 诸葛丽丽         <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">23</span> <span class="token operator">|</span>       <span class="token number">7</span> <span class="token operator">|</span> 河南           <span class="token operator">|</span>
<span class="token operator">|</span> s25318 <span class="token operator">|</span> 争青小子        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">26</span> <span class="token operator">|</span>       <span class="token number">6</span> <span class="token operator">|</span> 天津           <span class="token operator">|</span>
<span class="token operator">|</span> s25319 <span class="token operator">|</span> 梅超风        <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">23</span> <span class="token operator">|</span>       <span class="token number">5</span> <span class="token operator">|</span> 河北          <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
<span class="token number">4</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><h4 id="_1-3-3exists和not-exists"><a href="#_1-3-3exists和not-exists" class="header-anchor">#</a> 1.3.3	exists和not exists</h4> <p>1、	如果有人笔试超过80分就显示所有的学生</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stuinfo <span class="token keyword">where</span> <span class="token keyword">exists</span> <span class="token punctuation">(</span><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stumarks <span class="token keyword">where</span> writtenexam<span class="token operator">&gt;=</span><span class="token number">80</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> stuNo  <span class="token operator">|</span> stuName  <span class="token operator">|</span> stuSex <span class="token operator">|</span> stuAge <span class="token operator">|</span> stuSeat <span class="token operator">|</span> stuAddress <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> s25301 <span class="token operator">|</span> 张秋丽         <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">18</span> <span class="token operator">|</span>       <span class="token number">1</span> <span class="token operator">|</span> 北京           <span class="token operator">|</span>
<span class="token operator">|</span> s25302 <span class="token operator">|</span> 李文才        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">31</span> <span class="token operator">|</span>       <span class="token number">3</span> <span class="token operator">|</span> 上海          <span class="token operator">|</span>
<span class="token operator">|</span> s25303 <span class="token operator">|</span> 李斯文        <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">22</span> <span class="token operator">|</span>       <span class="token number">2</span> <span class="token operator">|</span> 北京           <span class="token operator">|</span>
<span class="token operator">|</span> s25304 <span class="token operator">|</span> 欧阳俊雄        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">28</span> <span class="token operator">|</span>       <span class="token number">4</span> <span class="token operator">|</span> 天津           <span class="token operator">|</span>
<span class="token operator">|</span> s25305 <span class="token operator">|</span> 诸葛丽丽         <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">23</span> <span class="token operator">|</span>       <span class="token number">7</span> <span class="token operator">|</span> 河南           <span class="token operator">|</span>
<span class="token operator">|</span> s25318 <span class="token operator">|</span> 争青小子        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">26</span> <span class="token operator">|</span>       <span class="token number">6</span> <span class="token operator">|</span> 天津           <span class="token operator">|</span>
<span class="token operator">|</span> s25319 <span class="token operator">|</span> 梅超风        <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">23</span> <span class="token operator">|</span>       <span class="token number">5</span> <span class="token operator">|</span> 河北          <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
</code></pre></div><p>2、	如果没有人超过80分就显示所有的学生</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stuinfo <span class="token keyword">where</span> <span class="token operator">not</span> <span class="token keyword">exists</span> <span class="token punctuation">(</span><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stumarks <span class="token keyword">where</span> writtenexam<span class="token operator">&gt;=</span><span class="token number">80</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
Empty <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.02</span> sec<span class="token punctuation">)</span>
</code></pre></div><h4 id="_1-3-4子查询分类"><a href="#_1-3-4子查询分类" class="header-anchor">#</a> 1.3.4	子查询分类</h4> <p>1、标量子查询：子查询返回的结果就一个</p> <p>2、列子查询：子查询返回的结果是一个列表</p> <p>3、行子查询：子查询返回的结果是一行</p> <p>例题：查询成绩最高的男生和女生</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> stuname<span class="token punctuation">,</span>stusex<span class="token punctuation">,</span>ch <span class="token keyword">from</span> stu <span class="token keyword">where</span> <span class="token punctuation">(</span>stusex<span class="token punctuation">,</span>ch<span class="token punctuation">)</span> <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token keyword">select</span> stusex<span class="token punctuation">,</span><span class="token function">max</span><span class="token punctuation">(</span>ch<span class="token punctuation">)</span> <span class="token keyword">from</span> stu <span class="token keyword">group</span> <span class="token keyword">by</span> stusex<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">----------+--------+------+</span>
<span class="token operator">|</span> stuname  <span class="token operator">|</span> stusex <span class="token operator">|</span> ch   <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------+--------+------+</span>
<span class="token operator">|</span> 争青小子        <span class="token operator">|</span> 男       <span class="token operator">|</span>   <span class="token number">86</span> <span class="token operator">|</span>
<span class="token operator">|</span> Tabm     <span class="token operator">|</span> 女      <span class="token operator">|</span>   <span class="token number">88</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------+--------+------+</span>
</code></pre></div><p>4、表子查询：子查询返回的结果当成一个表</p> <p>例题：查询成绩最高的男生和女生</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> stuname<span class="token punctuation">,</span>stusex<span class="token punctuation">,</span>ch <span class="token keyword">from</span> <span class="token punctuation">(</span><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stu <span class="token keyword">order</span> <span class="token keyword">by</span> ch <span class="token keyword">desc</span><span class="token punctuation">)</span> <span class="token keyword">as</span> t <span class="token keyword">group</span> <span class="token keyword">by</span> stusex<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">----------+--------+------+</span>
<span class="token operator">|</span> stuname  <span class="token operator">|</span> stusex <span class="token operator">|</span> ch   <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------+--------+------+</span>
<span class="token operator">|</span> Tabm     <span class="token operator">|</span> 女      <span class="token operator">|</span>   <span class="token number">88</span> <span class="token operator">|</span>
<span class="token operator">|</span> 争青小子        <span class="token operator">|</span> 男       <span class="token operator">|</span>   <span class="token number">86</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------+--------+------+</span>
</code></pre></div><div class="language- extra-class"><pre class="language-text"><code>脚下留心：from后面是一个表，如果子查询的结果当成表来看，必须将子查询的结果取别名。
</code></pre></div><h2 id="_1-4视图【view】"><a href="#_1-4视图【view】" class="header-anchor">#</a> 1.4	视图【view】</h2> <p>1、	视图是一张虚拟表，它表示一张表的部分或多张表的综合的结构。</p> <p>2、	视图仅仅是表结构，没有表数据。视图的结构和数据建立在表的基础上。</p> <h4 id="_1-4-1创建视图"><a href="#_1-4-1创建视图" class="header-anchor">#</a> 1.4.1	创建视图</h4> <p>语法</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token punctuation">[</span><span class="token operator">or</span> <span class="token keyword">replace</span><span class="token punctuation">]</span> <span class="token keyword">view</span> 视图的名称
<span class="token keyword">as</span>
	<span class="token keyword">select</span>语句
</code></pre></div><p>例题：</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">create</span> <span class="token keyword">view</span> vw_stu
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">as</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">select</span> stuname<span class="token punctuation">,</span>stusex<span class="token punctuation">,</span>writtenexam<span class="token punctuation">,</span>labexam <span class="token keyword">from</span> stuinfo <span class="token keyword">inner</span> <span class="token keyword">join</span> stumarks <span class="token keyword">using</span><span class="token punctuation">(</span>stuno<span class="token punctuation">)</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><div class="language- extra-class"><pre class="language-text"><code>多学一招：因为视图是一个表结构，所以创建视图后，会在数据库文件夹中多一个与视图名同名的.frm文件
</code></pre></div><h4 id="_1-4-2使用视图"><a href="#_1-4-2使用视图" class="header-anchor">#</a> 1.4.2	使用视图</h4> <p>视图是一张虚拟表，视图的用法和表的用法一样</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> vw_stu<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">----------+--------+-------------+---------+</span>
<span class="token operator">|</span> stuname  <span class="token operator">|</span> stusex <span class="token operator">|</span> writtenexam <span class="token operator">|</span> labexam <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------+--------+-------------+---------+</span>
<span class="token operator">|</span> 李斯文        <span class="token operator">|</span> 女      <span class="token operator">|</span>          <span class="token number">80</span> <span class="token operator">|</span>      <span class="token number">58</span> <span class="token operator">|</span>
<span class="token operator">|</span> 李文才        <span class="token operator">|</span> 男       <span class="token operator">|</span>          <span class="token number">50</span> <span class="token operator">|</span>      <span class="token number">90</span> <span class="token operator">|</span>
<span class="token operator">|</span> 欧阳俊雄        <span class="token operator">|</span> 男       <span class="token operator">|</span>          <span class="token number">65</span> <span class="token operator">|</span>      <span class="token number">50</span> <span class="token operator">|</span>
<span class="token operator">|</span> 张秋丽         <span class="token operator">|</span> 男       <span class="token operator">|</span>          <span class="token number">77</span> <span class="token operator">|</span>      <span class="token number">82</span> <span class="token operator">|</span>
<span class="token operator">|</span> 争青小子        <span class="token operator">|</span> 男       <span class="token operator">|</span>          <span class="token number">56</span> <span class="token operator">|</span>      <span class="token number">48</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------+--------+-------------+---------+</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">update</span> vw_stu <span class="token keyword">set</span> writtenexam<span class="token operator">=</span><span class="token number">88</span> <span class="token keyword">where</span> stuname<span class="token operator">=</span><span class="token string">'李斯文'</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">1</span> <span class="token keyword">row</span> affected <span class="token punctuation">(</span><span class="token number">0.05</span> sec<span class="token punctuation">)</span>
<span class="token keyword">Rows</span> <span class="token keyword">matched</span>: <span class="token number">1</span>  Changed: <span class="token number">1</span>  <span class="token keyword">Warnings</span>: <span class="token number">0</span>
</code></pre></div><h4 id="_1-4-3查看视图的结构"><a href="#_1-4-3查看视图的结构" class="header-anchor">#</a> 1.4.3	查看视图的结构</h4> <p>语法：</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">desc</span> 视图名
</code></pre></div><p>例题</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">desc</span> vw_stu<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-------------+-------------+------+-----+---------+-------+</span>
<span class="token operator">|</span> Field       <span class="token operator">|</span> <span class="token keyword">Type</span>        <span class="token operator">|</span> <span class="token boolean">Null</span> <span class="token operator">|</span> <span class="token keyword">Key</span> <span class="token operator">|</span> <span class="token keyword">Default</span> <span class="token operator">|</span> Extra <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-------------+-------------+------+-----+---------+-------+</span>
<span class="token operator">|</span> stuname     <span class="token operator">|</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token operator">|</span> <span class="token keyword">NO</span>   <span class="token operator">|</span>     <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">|</span> stusex      <span class="token operator">|</span> <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span>     <span class="token operator">|</span> <span class="token keyword">NO</span>   <span class="token operator">|</span>     <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">|</span> writtenexam <span class="token operator">|</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span>     <span class="token operator">|</span> YES  <span class="token operator">|</span>     <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">|</span> labexam     <span class="token operator">|</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span>     <span class="token operator">|</span> YES  <span class="token operator">|</span>     <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-------------+-------------+------+-----+---------+-------+</span>
</code></pre></div><h4 id="_1-4-4查看创建视图的语法"><a href="#_1-4-4查看创建视图的语法" class="header-anchor">#</a> 1.4.4	查看创建视图的语法</h4> <p>语法：</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">show</span> <span class="token keyword">create</span> <span class="token keyword">view</span> 视图名
</code></pre></div><p>例题</p> <p><img src="http://static.zhequtao.com/study/mysql/day4/1537067071676.png" alt="1537067071676"></p> <h4 id="_1-4-5显示所有视图"><a href="#_1-4-5显示所有视图" class="header-anchor">#</a> 1.4.5	显示所有视图</h4> <div class="language-sql extra-class"><pre class="language-sql"><code> <span class="token comment">#方法一：</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">show</span> <span class="token keyword">tables</span><span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">------------------+</span>
<span class="token operator">|</span> Tables_in_itcast <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------------------+</span>
<span class="token operator">|</span> stu              <span class="token operator">|</span>
<span class="token operator">|</span> stuinfo          <span class="token operator">|</span>
<span class="token operator">|</span> stumarks         <span class="token operator">|</span>
<span class="token operator">|</span> t1               <span class="token operator">|</span>
<span class="token operator">|</span> t2               <span class="token operator">|</span>
<span class="token operator">|</span> vw_stu           <span class="token operator">|</span>

<span class="token comment"># 方法二</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> table_name <span class="token keyword">from</span> information_schema<span class="token punctuation">.</span>views<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">------------+</span>
<span class="token operator">|</span> table_name <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------------+</span>
<span class="token operator">|</span> vw_stu     <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.05</span> sec<span class="token punctuation">)</span>
<span class="token operator">+</span><span class="token comment">------------------+</span>

<span class="token comment">#方法三</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">show</span> <span class="token keyword">table</span> <span class="token keyword">status</span> <span class="token keyword">where</span> <span class="token keyword">comment</span><span class="token operator">=</span><span class="token string">'view'</span> \G
<span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span> <span class="token number">1.</span> <span class="token keyword">row</span> <span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span>
           Name: vw_stu
         <span class="token keyword">Engine</span>: <span class="token boolean">NULL</span>
        Version: <span class="token boolean">NULL</span>
     Row_format: <span class="token boolean">NULL</span>
           <span class="token keyword">Rows</span>: <span class="token boolean">NULL</span>
 Avg_row_length: <span class="token boolean">NULL</span>
    Data_length: <span class="token boolean">NULL</span>
Max_data_length: <span class="token boolean">NULL</span>
   Index_length: <span class="token boolean">NULL</span>
      Data_free: <span class="token boolean">NULL</span>
 <span class="token keyword">Auto_increment</span>: <span class="token boolean">NULL</span>
    Create_time: <span class="token boolean">NULL</span>
    Update_time: <span class="token boolean">NULL</span>
     Check_time: <span class="token boolean">NULL</span>
      Collation: <span class="token boolean">NULL</span>
       Checksum: <span class="token boolean">NULL</span>
 Create_options: <span class="token boolean">NULL</span>
        <span class="token keyword">Comment</span>: <span class="token keyword">VIEW</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

</code></pre></div><h4 id="_1-4-6更改视图"><a href="#_1-4-6更改视图" class="header-anchor">#</a> 1.4.6	更改视图</h4> <p>语法：</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">alter</span> <span class="token keyword">view</span> 视图名
<span class="token keyword">as</span>
	<span class="token keyword">select</span> 语句
</code></pre></div><p>例题：</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">alter</span> <span class="token keyword">view</span> vw_stu
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">as</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stuinfo<span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><h4 id="_1-4-7删除视图"><a href="#_1-4-7删除视图" class="header-anchor">#</a> 1.4.7	删除视图</h4> <p>语法：</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">drop</span> <span class="token keyword">view</span> <span class="token punctuation">[</span><span class="token keyword">if</span> <span class="token keyword">exists</span><span class="token punctuation">]</span> 视图<span class="token number">1</span><span class="token punctuation">,</span>视图<span class="token number">2</span><span class="token punctuation">,</span>…
</code></pre></div><p>例题</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">drop</span> <span class="token keyword">view</span> vw_stu<span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><h4 id="_1-4-8视图的作用"><a href="#_1-4-8视图的作用" class="header-anchor">#</a> 1.4.8	视图的作用</h4> <ol><li>筛选数据，防止未经许可访问敏感数据</li> <li>隐藏表结构</li> <li>降低SQL语句的复杂度</li></ol> <h4 id="_1-4-9视图的算法"><a href="#_1-4-9视图的算法" class="header-anchor">#</a> 1.4.9	视图的算法</h4> <p>场景：找出语文成绩最高的男生和女生</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> <span class="token punctuation">(</span><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stu <span class="token keyword">order</span> <span class="token keyword">by</span> ch <span class="token keyword">desc</span><span class="token punctuation">)</span> <span class="token keyword">as</span> t <span class="token keyword">group</span> <span class="token keyword">by</span> stusex<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+------+------+</span>
<span class="token operator">|</span> stuNo  <span class="token operator">|</span> stuName  <span class="token operator">|</span> stuSex <span class="token operator">|</span> stuAge <span class="token operator">|</span> stuSeat <span class="token operator">|</span> stuAddress <span class="token operator">|</span> ch   <span class="token operator">|</span> math <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+------+------+</span>
<span class="token operator">|</span> s25321 <span class="token operator">|</span> Tabm     <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">23</span> <span class="token operator">|</span>       <span class="token number">9</span> <span class="token operator">|</span> 河北          <span class="token operator">|</span>   <span class="token number">88</span> <span class="token operator">|</span>   <span class="token number">77</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25318 <span class="token operator">|</span> 争青小子        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">26</span> <span class="token operator">|</span>       <span class="token number">6</span> <span class="token operator">|</span> 天津           <span class="token operator">|</span>   <span class="token number">86</span> <span class="token operator">|</span>   <span class="token number">92</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+------+------+</span>
</code></pre></div><p>我们可以将子查询封装到视图中</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">create</span> <span class="token keyword">view</span> vw_stu
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">as</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stu <span class="token keyword">order</span> <span class="token keyword">by</span> ch <span class="token keyword">desc</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><p>可以将上面的子查询更改成视图，但是，结果和上面不一样</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> vw_stu <span class="token keyword">group</span> <span class="token keyword">by</span> stusex<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">--------+---------+--------+--------+---------+------------+------+------+</span>
<span class="token operator">|</span> stuNo  <span class="token operator">|</span> stuName <span class="token operator">|</span> stuSex <span class="token operator">|</span> stuAge <span class="token operator">|</span> stuSeat <span class="token operator">|</span> stuAddress <span class="token operator">|</span> ch   <span class="token operator">|</span> math <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+---------+--------+--------+---------+------------+------+------+</span>
<span class="token operator">|</span> s25301 <span class="token operator">|</span> 张秋丽        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">18</span> <span class="token operator">|</span>       <span class="token number">1</span> <span class="token operator">|</span> 北京           <span class="token operator">|</span>   <span class="token number">80</span> <span class="token operator">|</span> <span class="token boolean">NULL</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25303 <span class="token operator">|</span> 李斯文       <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">22</span> <span class="token operator">|</span>       <span class="token number">2</span> <span class="token operator">|</span> 北京           <span class="token operator">|</span>   <span class="token number">55</span> <span class="token operator">|</span>   <span class="token number">82</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+---------+--------+--------+---------+------------+------+------+</span>
</code></pre></div><p>原因：这是因为视图的算法造成的</p> <div class="language- extra-class"><pre class="language-text"><code>1. merge：合并算法，将视图的语句和外层的语句合并后在执行。
2. temptable：临时表算法，将视图生成一个临时表，再执行外层语句
3. undefined：未定义，MySQL到底用merge还是用temptable由MySQL决定，这是一个默认的算法，一般视图都会选择merge算法，因为merge效率高。
</code></pre></div><p>解决：在创建视图的时候指定视图的算法</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">algorithm</span><span class="token operator">=</span><span class="token keyword">temptable</span> <span class="token keyword">view</span> 视图名
<span class="token keyword">as</span>
	<span class="token keyword">select</span> 语句
</code></pre></div><p>指定算法创建视图</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">create</span> <span class="token keyword">algorithm</span><span class="token operator">=</span><span class="token keyword">temptable</span> <span class="token keyword">view</span> vw_stu
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">as</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span>  <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stu <span class="token keyword">order</span> <span class="token keyword">by</span> ch <span class="token keyword">desc</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> vw_stu <span class="token keyword">group</span> <span class="token keyword">by</span> stusex<span class="token punctuation">;</span>   <span class="token comment"># 结果是一致的</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+------+------+</span>
<span class="token operator">|</span> stuNo  <span class="token operator">|</span> stuName  <span class="token operator">|</span> stuSex <span class="token operator">|</span> stuAge <span class="token operator">|</span> stuSeat <span class="token operator">|</span> stuAddress <span class="token operator">|</span> ch   <span class="token operator">|</span> math <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+------+------+</span>
<span class="token operator">|</span> s25321 <span class="token operator">|</span> Tabm     <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">23</span> <span class="token operator">|</span>       <span class="token number">9</span> <span class="token operator">|</span> 河北          <span class="token operator">|</span>   <span class="token number">88</span> <span class="token operator">|</span>   <span class="token number">77</span> <span class="token operator">|</span>
<span class="token operator">|</span> s25318 <span class="token operator">|</span> 争青小子        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">26</span> <span class="token operator">|</span>       <span class="token number">6</span> <span class="token operator">|</span> 天津           <span class="token operator">|</span>   <span class="token number">86</span> <span class="token operator">|</span>   <span class="token number">92</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+------+------+</span>
</code></pre></div><h2 id="_1-5事务【transaction】"><a href="#_1-5事务【transaction】" class="header-anchor">#</a> 1.5	事务【transaction】</h2> <ol><li>事务是一个不可分割的执行单元</li> <li>事务作为一个整体要么一起执行，要么一起回滚</li></ol> <p>插入测试数据</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">create</span> <span class="token keyword">table</span> bank<span class="token punctuation">(</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> cardid <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">)</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> money <span class="token keyword">int</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token punctuation">)</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">insert</span> <span class="token keyword">into</span> bank <span class="token keyword">values</span> <span class="token punctuation">(</span><span class="token string">'1001'</span><span class="token punctuation">,</span><span class="token number">1000</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token string">'1002'</span><span class="token punctuation">,</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">2</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
Records: <span class="token number">2</span>  Duplicates: <span class="token number">0</span>  <span class="token keyword">Warnings</span>: <span class="token number">0</span>
</code></pre></div><h4 id="_1-5-1事务操作"><a href="#_1-5-1事务操作" class="header-anchor">#</a> 1.5.1	事务操作</h4> <div class="language-sql extra-class"><pre class="language-sql"><code>开启事务：<span class="token keyword">start</span> <span class="token keyword">transaction</span>或<span class="token keyword">begin</span> <span class="token punctuation">[</span><span class="token keyword">work</span><span class="token punctuation">]</span>
提交事务：<span class="token keyword">commit</span>
回滚事务：<span class="token keyword">rollback</span>
</code></pre></div><p>例题：</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">delimiter</span> <span class="token comment">//            # 更改定界符</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">start</span> <span class="token keyword">transaction</span><span class="token punctuation">;</span>			<span class="token comment"># 开启事务</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">update</span> bank <span class="token keyword">set</span> money<span class="token operator">=</span>money<span class="token operator">-</span><span class="token number">100</span> <span class="token keyword">where</span> cardid<span class="token operator">=</span><span class="token string">'1001'</span><span class="token punctuation">;</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">update</span> bank <span class="token keyword">set</span> money<span class="token operator">=</span>money<span class="token operator">+</span><span class="token number">100</span> <span class="token keyword">where</span> cardid<span class="token operator">=</span><span class="token string">'1002'</span>  <span class="token comment">//</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">commit</span> <span class="token comment">//   # 提交事务</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">rollback</span> <span class="token comment">//  # 回滚事务</span>
</code></pre></div><div class="language- extra-class"><pre class="language-text"><code>思考：事务什么时候产生？什么时候结束？
答：开启的时候产生，提交事务或回滚事务都结束

脚下留心：只有innodb和BDB才支持事务，myisam不支持事务。
</code></pre></div><h4 id="_1-5-2-设置事务的回滚点"><a href="#_1-5-2-设置事务的回滚点" class="header-anchor">#</a> 1.5.2      设置事务的回滚点</h4> <p>语法：</p> <div class="language-sql extra-class"><pre class="language-sql"><code>设置回滚点： <span class="token keyword">savepoint</span> 回滚点名
回滚到回滚点： <span class="token keyword">rollback</span> <span class="token keyword">to</span> 回滚点
</code></pre></div><p>例题：</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span>  <span class="token keyword">start</span> <span class="token keyword">transaction</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">insert</span> <span class="token keyword">into</span> bank <span class="token keyword">values</span> <span class="token punctuation">(</span><span class="token string">'1003'</span><span class="token punctuation">,</span><span class="token number">1000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">1</span> <span class="token keyword">row</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">savepoint</span> aa<span class="token punctuation">;</span>    <span class="token comment"># 设置回滚点 aa</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">insert</span> <span class="token keyword">into</span> bank <span class="token keyword">values</span> <span class="token punctuation">(</span><span class="token string">'1004'</span><span class="token punctuation">,</span><span class="token number">500</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">1</span> <span class="token keyword">row</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
 
mysql<span class="token operator">&gt;</span> <span class="token keyword">savepoint</span> bb<span class="token punctuation">;</span>   <span class="token comment"># 设置回滚点bb</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
 
mysql<span class="token operator">&gt;</span> <span class="token keyword">rollback</span> <span class="token keyword">to</span> aa<span class="token punctuation">;</span>    <span class="token comment"># 回滚到aa点</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">commit</span><span class="token punctuation">;</span>    <span class="token comment"># 提交事务</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> bank <span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">--------+-------+</span>
<span class="token operator">|</span> cardid <span class="token operator">|</span> money <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+-------+</span>
<span class="token operator">|</span> <span class="token number">1001</span>   <span class="token operator">|</span>   <span class="token number">800</span> <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token number">1002</span>   <span class="token operator">|</span>   <span class="token number">200</span> <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token number">1003</span>   <span class="token operator">|</span>  <span class="token number">1000</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+-------+</span>
</code></pre></div><h4 id="_1-5-3事务的特性（acid）"><a href="#_1-5-3事务的特性（acid）" class="header-anchor">#</a> 1.5.3	事务的特性（ACID）</h4> <ol><li>原子性（Atomicity）：事务是一个整体，不可以再分，要么一起执行，要么一起不执行。</li> <li>一致性（Consistency）：事务完成时，数据必须处于一致的状态。</li> <li>隔离性（Isolation）：每个事务都是相互隔离的</li> <li>永久性（Durability）：事务完成后，对数据的修改是永久性的。</li></ol> <h2 id="_1-6索引【index】"><a href="#_1-6索引【index】" class="header-anchor">#</a> 1.6	索引【index】</h2> <p>索引的优点：查询速度快</p> <p>索引的缺点：</p> <ol><li>增、删、改（数据操作语句）效率低了</li> <li>索引占用空间</li></ol> <h4 id="_1-6-1索引的类型"><a href="#_1-6-1索引的类型" class="header-anchor">#</a> 1.6.1	索引的类型</h4> <ol><li><p>普通索引</p></li> <li><p>唯一索引（唯一键）</p></li> <li><p>主键索引：只要主键就自动创建主键索引，不需要手动创建。</p></li> <li><p>全文索引，搜索引擎使用，MySQL不支持中文的全文索引，我们通过sphinx去解决中文的全文索引。</p></li></ol> <h4 id="_1-6-2创建普通索引【create-index】"><a href="#_1-6-2创建普通索引【create-index】" class="header-anchor">#</a> 1.6.2	创建普通索引【create index】</h4> <p>语法：</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">index</span> <span class="token punctuation">[</span>索引名<span class="token punctuation">]</span> <span class="token keyword">on</span> 表名 （字段名）
<span class="token keyword">alter</span> <span class="token keyword">table</span> 表名 <span class="token keyword">add</span> <span class="token keyword">index</span> <span class="token punctuation">[</span>索引的名称<span class="token punctuation">]</span> （列名）
</code></pre></div><p>例题：</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment"># 创建索引方法一</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">create</span> <span class="token keyword">index</span> ix_stuname <span class="token keyword">on</span> stuinfo<span class="token punctuation">(</span>stuname<span class="token punctuation">)</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.08</span> sec<span class="token punctuation">)</span>
Records: <span class="token number">0</span>  Duplicates: <span class="token number">0</span>  <span class="token keyword">Warnings</span>: <span class="token number">0</span>

<span class="token comment"># 创建索引方法二</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">alter</span> <span class="token keyword">table</span> stuinfo <span class="token keyword">add</span> <span class="token keyword">index</span> ix_address <span class="token punctuation">(</span>stuaddress<span class="token punctuation">)</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.08</span> sec<span class="token punctuation">)</span>
Records: <span class="token number">0</span>  Duplicates: <span class="token number">0</span>  <span class="token keyword">Warnings</span>: <span class="token number">0</span>

<span class="token comment"># 创建表的时候就添加索引</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">create</span> <span class="token keyword">table</span> emp<span class="token punctuation">(</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> id <span class="token keyword">int</span><span class="token punctuation">,</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> name <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">index</span> ix_name <span class="token punctuation">(</span>name<span class="token punctuation">)</span>   <span class="token comment"># 创建索引</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token punctuation">)</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><h4 id="_1-6-3创建唯一索引"><a href="#_1-6-3创建唯一索引" class="header-anchor">#</a> 1.6.3	创建唯一索引</h4> <div class="language-sql extra-class"><pre class="language-sql"><code>语法一：<span class="token keyword">create</span> <span class="token keyword">unique</span> <span class="token keyword">index</span> 索引名 <span class="token keyword">on</span> 表名 （字段名）
语法二：<span class="token keyword">alter</span> <span class="token keyword">table</span> 表名 <span class="token keyword">add</span> unqiue <span class="token punctuation">[</span><span class="token keyword">index</span><span class="token punctuation">]</span> <span class="token punctuation">[</span>索引的名称<span class="token punctuation">]</span> （列名）
语法三：创建表的时候添加唯一索引，和创建唯一键是一样的。
</code></pre></div><p>例题</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment"># 方法一：</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">create</span> <span class="token keyword">unique</span> <span class="token keyword">index</span> UQ_stuname <span class="token keyword">on</span> stu<span class="token punctuation">(</span>stuname<span class="token punctuation">)</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.06</span> sec<span class="token punctuation">)</span>
Records: <span class="token number">0</span>  Duplicates: <span class="token number">0</span>  <span class="token keyword">Warnings</span>: <span class="token number">0</span>

<span class="token comment"># 方法二：</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">alter</span> <span class="token keyword">table</span> stu <span class="token keyword">add</span> <span class="token keyword">unique</span> UQ_address <span class="token punctuation">(</span>stuaddress<span class="token punctuation">)</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.02</span> sec<span class="token punctuation">)</span>
Records: <span class="token number">0</span>  Duplicates: <span class="token number">0</span>  <span class="token keyword">Warnings</span>: <span class="token number">0</span>

<span class="token comment"># 方法三</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">create</span> <span class="token keyword">table</span> stu2<span class="token punctuation">(</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> id <span class="token keyword">int</span><span class="token punctuation">,</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> name <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">unique</span> UQ_name<span class="token punctuation">(</span>name<span class="token punctuation">)</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token punctuation">)</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.01</span> sec<span class="token punctuation">)</span>
</code></pre></div><h4 id="_1-6-4删除索引"><a href="#_1-6-4删除索引" class="header-anchor">#</a> 1.6.4	删除索引</h4> <p>语法</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">drop</span> <span class="token keyword">index</span> 索引名 <span class="token keyword">on</span> 表名
</code></pre></div><p>例题</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">drop</span> <span class="token keyword">index</span> ix_stuname <span class="token keyword">on</span> stuinfo<span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.03</span> sec<span class="token punctuation">)</span>
Records: <span class="token number">0</span>  Duplicates: <span class="token number">0</span>  <span class="token keyword">Warnings</span>: <span class="token number">0</span>
</code></pre></div><h4 id="_1-6-5创建索引的指导原则"><a href="#_1-6-5创建索引的指导原则" class="header-anchor">#</a> 1.6.5	创建索引的指导原则</h4> <ol><li><p>该列用于频繁搜索</p></li> <li><p>改列用于排序</p></li> <li><p>公共字段要创建索引</p></li> <li><p>如果表中的数据很少，不需要创建索引。MySQL搜索索引的时间比逐条搜索数据的时间要长。</p></li> <li><p>如果一个字段上的数据只有几个不同的值，改字段不适合做索引，比如性别。</p></li></ol> <h2 id="_1-7-函数"><a href="#_1-7-函数" class="header-anchor">#</a> 1.7  函数</h2> <h4 id="_1-7-1-数字类"><a href="#_1-7-1-数字类" class="header-anchor">#</a> 1.7.1  数字类</h4> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> rand<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>			<span class="token comment"># 生成随机数</span>
<span class="token operator">+</span><span class="token comment">---------------------+</span>
<span class="token operator">|</span> rand<span class="token punctuation">(</span><span class="token punctuation">)</span>              <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">---------------------+</span>
<span class="token operator">|</span> <span class="token number">0.18474003969201822</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">---------------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stuinfo <span class="token keyword">order</span> <span class="token keyword">by</span> rand<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>   <span class="token comment"># 随机排序</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stuinfo <span class="token keyword">order</span> <span class="token keyword">by</span> rand<span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">limit</span> <span class="token number">2</span><span class="token punctuation">;</span>    <span class="token comment"># 随机抽两个学生</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> stuNo  <span class="token operator">|</span> stuName  <span class="token operator">|</span> stuSex <span class="token operator">|</span> stuAge <span class="token operator">|</span> stuSeat <span class="token operator">|</span> stuAddress <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> s25305 <span class="token operator">|</span> 诸葛丽丽         <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">23</span> <span class="token operator">|</span>       <span class="token number">7</span> <span class="token operator">|</span> 河南           <span class="token operator">|</span>
<span class="token operator">|</span> s25304 <span class="token operator">|</span> 欧阳俊雄        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">28</span> <span class="token operator">|</span>       <span class="token number">4</span> <span class="token operator">|</span> 天津           <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
<span class="token number">2</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token function">round</span><span class="token punctuation">(</span><span class="token number">3.5</span><span class="token punctuation">)</span><span class="token punctuation">;</span>     <span class="token comment">#四舍五入</span>
<span class="token operator">+</span><span class="token comment">------------+</span>
<span class="token operator">|</span> <span class="token function">round</span><span class="token punctuation">(</span><span class="token number">3.5</span><span class="token punctuation">)</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------------+</span>
<span class="token operator">|</span>          <span class="token number">4</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> ceil<span class="token punctuation">(</span><span class="token number">3.1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>	<span class="token comment"># 向上取整</span>
<span class="token operator">+</span><span class="token comment">-----------+</span>
<span class="token operator">|</span> ceil<span class="token punctuation">(</span><span class="token number">3.1</span><span class="token punctuation">)</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------+</span>
<span class="token operator">|</span>         <span class="token number">4</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> floor<span class="token punctuation">(</span><span class="token number">3.9</span><span class="token punctuation">)</span><span class="token punctuation">;</span>	<span class="token comment"># 向下取整</span>
<span class="token operator">+</span><span class="token comment">------------+</span>
<span class="token operator">|</span> floor<span class="token punctuation">(</span><span class="token number">3.9</span><span class="token punctuation">)</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------------+</span>
<span class="token operator">|</span>          <span class="token number">3</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token keyword">truncate</span><span class="token punctuation">(</span><span class="token number">3.1415926</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">;</span>	<span class="token comment"># 截取数字</span>
<span class="token operator">+</span><span class="token comment">-----------------------+</span>
<span class="token operator">|</span> <span class="token keyword">truncate</span><span class="token punctuation">(</span><span class="token number">3.1415926</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------------+</span>
<span class="token operator">|</span>                 <span class="token number">3.141</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><h4 id="_1-7-2-字符串类"><a href="#_1-7-2-字符串类" class="header-anchor">#</a> 1.7.2 字符串类</h4> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token function">ucase</span><span class="token punctuation">(</span><span class="token string">'i am a boy!'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>		<span class="token comment"># 转成大写</span>
<span class="token operator">+</span><span class="token comment">----------------------+</span>
<span class="token operator">|</span> <span class="token function">ucase</span><span class="token punctuation">(</span><span class="token string">'i am a boy!'</span><span class="token punctuation">)</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------------------+</span>
<span class="token operator">|</span> I AM A BOY<span class="token operator">!</span>          <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token function">lcase</span><span class="token punctuation">(</span><span class="token string">'I Am A Boy!'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>		<span class="token comment">#转成小写</span>
<span class="token operator">+</span><span class="token comment">----------------------+</span>
<span class="token operator">|</span> <span class="token function">lcase</span><span class="token punctuation">(</span><span class="token string">'I Am A Boy!'</span><span class="token punctuation">)</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------------------+</span>
<span class="token operator">|</span> i am a boy<span class="token operator">!</span>          <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token keyword">left</span><span class="token punctuation">(</span><span class="token string">'abcde'</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">;</span>		<span class="token comment"># 从左边开始截取，截取3个</span>
<span class="token operator">+</span><span class="token comment">-----------------+</span>
<span class="token operator">|</span> <span class="token keyword">left</span><span class="token punctuation">(</span><span class="token string">'abcde'</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------+</span>
<span class="token operator">|</span> abc             <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token keyword">right</span><span class="token punctuation">(</span><span class="token string">'abcde'</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">;</span>		<span class="token comment"># 从右边开始截取，截取3个</span>
<span class="token operator">+</span><span class="token comment">------------------+</span>
<span class="token operator">|</span> <span class="token keyword">right</span><span class="token punctuation">(</span><span class="token string">'abcde'</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------------------+</span>
<span class="token operator">|</span> cde              <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> substring<span class="token punctuation">(</span><span class="token string">'abcde'</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">;</span>	<span class="token comment">#从第2个位置开始截取，截取3个【位置从1开始】</span>
<span class="token operator">+</span><span class="token comment">------------------------+</span>
<span class="token operator">|</span> substring<span class="token punctuation">(</span><span class="token string">'abcde'</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------------------------+</span>
<span class="token operator">|</span> bcd                    <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------------------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> concat<span class="token punctuation">(</span><span class="token string">'中国'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>	<span class="token comment"># 字符串相连</span>
<span class="token operator">+</span><span class="token comment">-----------------------+</span>
<span class="token operator">|</span> concat<span class="token punctuation">(</span><span class="token string">'中国'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">)</span>       <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------------+</span>
<span class="token operator">|</span> 中国上海                    <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> concat<span class="token punctuation">(</span>stuname<span class="token punctuation">,</span><span class="token string">'-'</span><span class="token punctuation">,</span>stusex<span class="token punctuation">)</span> <span class="token keyword">from</span> stuinfo<span class="token punctuation">;</span>  <span class="token comment"># 将表中的姓名和性别连接起来</span>
<span class="token operator">+</span><span class="token comment">----------------------------+</span>
<span class="token operator">|</span> concat<span class="token punctuation">(</span>stuname<span class="token punctuation">,</span><span class="token string">'-'</span><span class="token punctuation">,</span>stusex<span class="token punctuation">)</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------------------------+</span>
<span class="token operator">|</span> 张秋丽<span class="token operator">-</span>男                          <span class="token operator">|</span>
<span class="token operator">|</span> 李文才<span class="token operator">-</span>男                         <span class="token operator">|</span>
<span class="token operator">|</span> 李斯文<span class="token operator">-</span>女                        <span class="token operator">|</span>
<span class="token operator">|</span> 欧阳俊雄<span class="token operator">-</span>男                         <span class="token operator">|</span>
<span class="token operator">|</span> 诸葛丽丽<span class="token operator">-</span>女                         <span class="token operator">|</span>
<span class="token operator">|</span> 争青小子<span class="token operator">-</span>男                         <span class="token operator">|</span>
<span class="token operator">|</span> 梅超风<span class="token operator">-</span>女                        <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------------------------+</span>
<span class="token number">7</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

<span class="token comment"># coalesce(字段1，字段2)  如果字段1不为空就显示字段1，否则，显示字段2</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> stuname<span class="token punctuation">,</span><span class="token keyword">coalesce</span><span class="token punctuation">(</span>writtenexam<span class="token punctuation">,</span><span class="token string">'缺考'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">coalesce</span><span class="token punctuation">(</span>labexam<span class="token punctuation">,</span><span class="token string">'缺考'</span><span class="token punctuation">)</span> <span class="token keyword">from</span> stuinfo <span class="token keyword">natural</span> <span class="token keyword">left</span> <span class="token keyword">join</span> stumarks<span class="token punctuation">;</span>   <span class="token comment"># 将考试成绩为空的显示为缺考</span>
<span class="token operator">+</span><span class="token comment">----------+------------------------------+--------------------------+</span>
<span class="token operator">|</span> stuname  <span class="token operator">|</span> <span class="token keyword">coalesce</span><span class="token punctuation">(</span>writtenexam<span class="token punctuation">,</span><span class="token string">'缺考'</span><span class="token punctuation">)</span>    <span class="token operator">|</span> <span class="token keyword">coalesce</span><span class="token punctuation">(</span>labexam<span class="token punctuation">,</span><span class="token string">'缺考'</span><span class="token punctuation">)</span>    <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------+------------------------------+--------------------------+</span>
<span class="token operator">|</span> 张秋丽         <span class="token operator">|</span> <span class="token number">77</span>                           <span class="token operator">|</span> <span class="token number">82</span>                       <span class="token operator">|</span>
<span class="token operator">|</span> 李文才        <span class="token operator">|</span> <span class="token number">50</span>                           <span class="token operator">|</span> <span class="token number">90</span>                       <span class="token operator">|</span>
<span class="token operator">|</span> 李斯文        <span class="token operator">|</span> <span class="token number">88</span>                           <span class="token operator">|</span> <span class="token number">58</span>                       <span class="token operator">|</span>
<span class="token operator">|</span> 欧阳俊雄        <span class="token operator">|</span> <span class="token number">65</span>                           <span class="token operator">|</span> <span class="token number">50</span>                       <span class="token operator">|</span>
<span class="token operator">|</span> 诸葛丽丽         <span class="token operator">|</span> 缺考                            <span class="token operator">|</span> 缺考                        <span class="token operator">|</span>
<span class="token operator">|</span> 争青小子        <span class="token operator">|</span> <span class="token number">56</span>                           <span class="token operator">|</span> <span class="token number">48</span>                       <span class="token operator">|</span>
<span class="token operator">|</span> 梅超风        <span class="token operator">|</span> 缺考                            <span class="token operator">|</span> 缺考                        <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------+------------------------------+--------------------------+</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> length<span class="token punctuation">(</span><span class="token string">'锄禾日当午'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>		<span class="token comment"># 字节长度</span>
<span class="token operator">+</span><span class="token comment">----------------------+</span>
<span class="token operator">|</span> length<span class="token punctuation">(</span><span class="token string">'锄禾日当午'</span><span class="token punctuation">)</span>          <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------------------+</span>
<span class="token operator">|</span>                   <span class="token number">10</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> char_length<span class="token punctuation">(</span><span class="token string">'锄禾日当午'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>		<span class="token comment"># 字符个数</span>
<span class="token operator">+</span><span class="token comment">---------------------------+</span>
<span class="token operator">|</span> char_length<span class="token punctuation">(</span><span class="token string">'锄禾日当午'</span><span class="token punctuation">)</span>          <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">---------------------------+</span>
<span class="token operator">|</span>                         <span class="token number">5</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">---------------------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><h4 id="_1-7-3-时间类"><a href="#_1-7-3-时间类" class="header-anchor">#</a> 1.7.3 时间类</h4> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> unix_timestamp<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>	<span class="token comment">#获取时间戳</span>
<span class="token operator">+</span><span class="token comment">------------------+</span>
<span class="token operator">|</span> unix_timestamp<span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------------------+</span>
<span class="token operator">|</span>       <span class="token number">1537084508</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> from_unixtime<span class="token punctuation">(</span>unix_timestamp<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>	<span class="token comment"># 将时间戳转成年-月-日 小时:分钟:秒的格式</span>
<span class="token operator">+</span><span class="token comment">---------------------------------+</span>
<span class="token operator">|</span> from_unixtime<span class="token punctuation">(</span>unix_timestamp<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">---------------------------------+</span>
<span class="token operator">|</span> <span class="token number">2018</span><span class="token operator">-</span><span class="token number">09</span><span class="token operator">-</span><span class="token number">16</span> <span class="token number">15</span>:<span class="token number">55</span>:<span class="token number">56</span>             <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">---------------------------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>		<span class="token comment"># 获取当前日期时间</span>
<span class="token operator">+</span><span class="token comment">---------------------+</span>
<span class="token operator">|</span> <span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span>               <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">---------------------+</span>
<span class="token operator">|</span> <span class="token number">2018</span><span class="token operator">-</span><span class="token number">09</span><span class="token operator">-</span><span class="token number">16</span> <span class="token number">15</span>:<span class="token number">57</span>:<span class="token number">04</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">---------------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token keyword">year</span><span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> 年<span class="token punctuation">,</span><span class="token keyword">month</span><span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> 月<span class="token punctuation">,</span> <span class="token keyword">day</span><span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> 日<span class="token punctuation">,</span><span class="token keyword">hour</span><span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> 小<span class="token punctuation">,</span><span class="token keyword">minute</span><span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> 分钟<span class="token punctuation">,</span><span class="token keyword">second</span><span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> 秒<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">------+------+------+------+------+------+</span>
<span class="token operator">|</span> 年     <span class="token operator">|</span> 月     <span class="token operator">|</span> 日     <span class="token operator">|</span> 小时   <span class="token operator">|</span> 分钟     <span class="token operator">|</span> 秒     <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+------+------+------+------+------+</span>
<span class="token operator">|</span> <span class="token number">2018</span> <span class="token operator">|</span>    <span class="token number">9</span> <span class="token operator">|</span>   <span class="token number">16</span> <span class="token operator">|</span>   <span class="token number">15</span> <span class="token operator">|</span>   <span class="token number">59</span> <span class="token operator">|</span>   <span class="token number">14</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+------+------+------+------+------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> dayname<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> 星期<span class="token punctuation">,</span>monthname<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">,</span>dayofyear<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> 本年的第几天<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">--------+------------------+--------------+</span>
<span class="token operator">|</span> 星期       <span class="token operator">|</span> monthname<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">|</span> 本年的第几天           <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+------------------+--------------+</span>
<span class="token operator">|</span> Sunday <span class="token operator">|</span> September        <span class="token operator">|</span>          <span class="token number">259</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+------------------+--------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> datediff<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token string">'2008-8-8'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>	<span class="token comment"># 日期相减</span>
<span class="token operator">+</span><span class="token comment">----------------------------+</span>
<span class="token operator">|</span> datediff<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token string">'2008-8-8'</span><span class="token punctuation">)</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------------------------+</span>
<span class="token operator">|</span>                       <span class="token number">3691</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------------------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token keyword">convert</span><span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">date</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">convert</span><span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">time</span><span class="token punctuation">)</span><span class="token punctuation">;</span>	<span class="token comment"># 将now()转成日期和时间</span>
<span class="token operator">+</span><span class="token comment">---------------------+---------------------+</span>
<span class="token operator">|</span> <span class="token keyword">convert</span><span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">date</span><span class="token punctuation">)</span> <span class="token operator">|</span> <span class="token keyword">convert</span><span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">time</span><span class="token punctuation">)</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">---------------------+---------------------+</span>
<span class="token operator">|</span> <span class="token number">2018</span><span class="token operator">-</span><span class="token number">09</span><span class="token operator">-</span><span class="token number">16</span>          <span class="token operator">|</span> <span class="token number">16</span>:<span class="token number">07</span>:<span class="token number">24</span>            <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">---------------------+---------------------+</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> cast<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token keyword">date</span><span class="token punctuation">)</span><span class="token punctuation">,</span>cast<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token keyword">time</span><span class="token punctuation">)</span><span class="token punctuation">;</span>   <span class="token comment"># 将now()转成日期和时间</span>
<span class="token operator">+</span><span class="token comment">---------------------+---------------------+</span>
<span class="token operator">|</span> cast<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token keyword">date</span><span class="token punctuation">)</span> <span class="token operator">|</span> cast<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token keyword">time</span><span class="token punctuation">)</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">---------------------+---------------------+</span>
<span class="token operator">|</span> <span class="token number">2018</span><span class="token operator">-</span><span class="token number">09</span><span class="token operator">-</span><span class="token number">16</span>          <span class="token operator">|</span> <span class="token number">16</span>:<span class="token number">08</span>:<span class="token number">03</span>            <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">---------------------+---------------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><h4 id="_1-7-4-加密函数"><a href="#_1-7-4-加密函数" class="header-anchor">#</a> 1.7.4 加密函数</h4> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token operator">+</span><span class="token comment">----------------------------------+------------------------------------------+</span>
<span class="token operator">|</span> md5<span class="token punctuation">(</span><span class="token string">'root'</span><span class="token punctuation">)</span>                      <span class="token operator">|</span> sha<span class="token punctuation">(</span><span class="token string">'root'</span><span class="token punctuation">)</span>                              <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------------------------------+------------------------------------------+</span>
<span class="token operator">|</span> <span class="token number">63</span>a9f0ea7bb98050796b649e85481845 <span class="token operator">|</span> dc76e9f0c0006e8f919e0c515c66dbba3982f785 <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------------------------------+------------------------------------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><h4 id="_1-7-5-判断函数"><a href="#_1-7-5-判断函数" class="header-anchor">#</a> 1.7.5 判断函数</h4> <p>语法</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">if</span><span class="token punctuation">(</span>表达式<span class="token punctuation">,</span>值<span class="token number">1</span><span class="token punctuation">,</span>值<span class="token number">2</span><span class="token punctuation">)</span>
</code></pre></div><p>例题：</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token keyword">if</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token operator">%</span><span class="token number">2</span><span class="token operator">=</span><span class="token number">0</span><span class="token punctuation">,</span><span class="token string">'偶数'</span><span class="token punctuation">,</span><span class="token string">'奇数'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">--------------------------+</span>
<span class="token operator">|</span> <span class="token keyword">if</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token operator">%</span><span class="token number">2</span><span class="token operator">=</span><span class="token number">0</span><span class="token punctuation">,</span><span class="token string">'偶数'</span><span class="token punctuation">,</span><span class="token string">'奇数'</span><span class="token punctuation">)</span>        <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------------------------+</span>
<span class="token operator">|</span> 偶数                        <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------------------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

<span class="token comment"># 语文和数学都超过60分才通过</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> stuname<span class="token punctuation">,</span>ch<span class="token punctuation">,</span>math<span class="token punctuation">,</span><span class="token keyword">if</span><span class="token punctuation">(</span>ch<span class="token operator">&gt;=</span><span class="token number">60</span> <span class="token operator">&amp;&amp;</span> math<span class="token operator">&gt;=</span><span class="token number">60</span><span class="token punctuation">,</span><span class="token string">'通过'</span><span class="token punctuation">,</span><span class="token string">'不通过'</span><span class="token punctuation">)</span> <span class="token string">'是否通过'</span> <span class="token keyword">from</span> stu<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">----------+------+------+----------+</span>
<span class="token operator">|</span> stuname  <span class="token operator">|</span> ch   <span class="token operator">|</span> math <span class="token operator">|</span> 是否通过       <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------+------+------+----------+</span>
<span class="token operator">|</span> 张秋丽         <span class="token operator">|</span>   <span class="token number">80</span> <span class="token operator">|</span> <span class="token boolean">NULL</span> <span class="token operator">|</span> 不通过        <span class="token operator">|</span>
<span class="token operator">|</span> 李文才        <span class="token operator">|</span>   <span class="token number">77</span> <span class="token operator">|</span>   <span class="token number">76</span> <span class="token operator">|</span> 通过        <span class="token operator">|</span>
<span class="token operator">|</span> 李斯文        <span class="token operator">|</span>   <span class="token number">55</span> <span class="token operator">|</span>   <span class="token number">82</span> <span class="token operator">|</span> 不通过        <span class="token operator">|</span>
<span class="token operator">|</span> 欧阳俊雄        <span class="token operator">|</span> <span class="token boolean">NULL</span> <span class="token operator">|</span>   <span class="token number">74</span> <span class="token operator">|</span> 不通过        <span class="token operator">|</span>
<span class="token operator">|</span> 诸葛丽丽         <span class="token operator">|</span>   <span class="token number">72</span> <span class="token operator">|</span>   <span class="token number">56</span> <span class="token operator">|</span> 不通过        <span class="token operator">|</span>
<span class="token operator">|</span> 争青小子        <span class="token operator">|</span>   <span class="token number">86</span> <span class="token operator">|</span>   <span class="token number">92</span> <span class="token operator">|</span> 通过        <span class="token operator">|</span>
<span class="token operator">|</span> 梅超风        <span class="token operator">|</span>   <span class="token number">74</span> <span class="token operator">|</span>   <span class="token number">67</span> <span class="token operator">|</span> 通过        <span class="token operator">|</span>
<span class="token operator">|</span> Tom      <span class="token operator">|</span>   <span class="token number">65</span> <span class="token operator">|</span>   <span class="token number">67</span> <span class="token operator">|</span> 通过        <span class="token operator">|</span>
<span class="token operator">|</span> Tabm     <span class="token operator">|</span>   <span class="token number">88</span> <span class="token operator">|</span>   <span class="token number">77</span> <span class="token operator">|</span> 通过        <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----------+------+------+----------+</span>
<span class="token number">9</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><h2 id="_1-8-预处理"><a href="#_1-8-预处理" class="header-anchor">#</a> 1.8  预处理</h2> <p>预编译一次，可以多次执行。用来解决一条SQL语句频繁执行的问题。</p> <div class="language- extra-class"><pre class="language-text"><code>预处理语句：prepare 预处理名字 from ‘sql语句’
执行预处理：execute 预处理名字 [using 变量]
</code></pre></div><p>例题一：</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">prepare</span> stmt <span class="token keyword">from</span> <span class="token string">'select * from stuinfo'</span><span class="token punctuation">;</span>	<span class="token comment"># 创建预处理</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
Statement prepared

mysql<span class="token operator">&gt;</span> <span class="token keyword">execute</span> stmt<span class="token punctuation">;</span>	<span class="token comment"># 执行预处理</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> stuNo  <span class="token operator">|</span> stuName  <span class="token operator">|</span> stuSex <span class="token operator">|</span> stuAge <span class="token operator">|</span> stuSeat <span class="token operator">|</span> stuAddress <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> s25301 <span class="token operator">|</span> 张秋丽         <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">18</span> <span class="token operator">|</span>       <span class="token number">1</span> <span class="token operator">|</span> 北京           <span class="token operator">|</span>
<span class="token operator">|</span> s25302 <span class="token operator">|</span> 李文才        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">31</span> <span class="token operator">|</span>       <span class="token number">3</span> <span class="token operator">|</span> 上海          <span class="token operator">|</span>
<span class="token operator">|</span> s25303 <span class="token operator">|</span> 李斯文        <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">22</span> <span class="token operator">|</span>       <span class="token number">2</span> <span class="token operator">|</span> 北京           <span class="token operator">|</span>
<span class="token operator">|</span> s25304 <span class="token operator">|</span> 欧阳俊雄        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">28</span> <span class="token operator">|</span>       <span class="token number">4</span> <span class="token operator">|</span> 天津           <span class="token operator">|</span>
<span class="token operator">|</span> s25305 <span class="token operator">|</span> 诸葛丽丽         <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">23</span> <span class="token operator">|</span>       <span class="token number">7</span> <span class="token operator">|</span> 河南           <span class="token operator">|</span>
<span class="token operator">|</span> s25318 <span class="token operator">|</span> 争青小子        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">26</span> <span class="token operator">|</span>       <span class="token number">6</span> <span class="token operator">|</span> 天津           <span class="token operator">|</span>
<span class="token operator">|</span> s25319 <span class="token operator">|</span> 梅超风        <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">23</span> <span class="token operator">|</span>       <span class="token number">5</span> <span class="token operator">|</span> 河北          <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
<span class="token number">7</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><p>例题二：传递参数</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">delimiter</span> <span class="token comment">// </span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">prepare</span> stmt <span class="token keyword">from</span> <span class="token string">'select * from stuinfo where stuno=?'</span> <span class="token comment">// -- ?是位置占位符</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
Statement prepared

mysql<span class="token operator">&gt;</span> <span class="token keyword">set</span> <span class="token variable">@id</span><span class="token operator">=</span><span class="token string">'s25301'</span><span class="token punctuation">;</span>       <span class="token comment">-- 变量以@开头，通过set给变量赋值</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">execute</span> stmt <span class="token keyword">using</span> <span class="token variable">@id</span> <span class="token comment">//  -- 执行预处理，传递参数</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

<span class="token operator">+</span><span class="token comment">--------+---------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> stuNo  <span class="token operator">|</span> stuName <span class="token operator">|</span> stuSex <span class="token operator">|</span> stuAge <span class="token operator">|</span> stuSeat <span class="token operator">|</span> stuAddress <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+---------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> s25301 <span class="token operator">|</span> 张秋丽        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">18</span> <span class="token operator">|</span>       <span class="token number">1</span> <span class="token operator">|</span> 北京           <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+---------+--------+--------+---------+------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><div class="language- extra-class"><pre class="language-text"><code>脚下留心：
1、?是位置占位符
2、变量以@开头
3、通过set给变量赋值
</code></pre></div><p>例题三：传递多个参数</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">prepare</span> stmt <span class="token keyword">from</span> <span class="token string">'select * from stuinfo where stusex=? and stuaddress=?'</span>  <span class="token comment">//</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
Statement prepared

mysql<span class="token operator">&gt;</span> <span class="token keyword">set</span> <span class="token variable">@sex</span><span class="token operator">=</span><span class="token string">'男'</span><span class="token punctuation">;</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">set</span> <span class="token variable">@addr</span><span class="token operator">=</span><span class="token string">'北京'</span><span class="token punctuation">;</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">execute</span> stmt <span class="token keyword">using</span> <span class="token variable">@sex</span><span class="token punctuation">,</span><span class="token variable">@addr</span> <span class="token comment">//</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

<span class="token operator">+</span><span class="token comment">--------+---------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> stuNo  <span class="token operator">|</span> stuName <span class="token operator">|</span> stuSex <span class="token operator">|</span> stuAge <span class="token operator">|</span> stuSeat <span class="token operator">|</span> stuAddress <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+---------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> s25301 <span class="token operator">|</span> 张秋丽        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">18</span> <span class="token operator">|</span>       <span class="token number">1</span> <span class="token operator">|</span> 北京           <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+---------+--------+--------+---------+------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><h2 id="_1-9存储过程【procedure】"><a href="#_1-9存储过程【procedure】" class="header-anchor">#</a> 1.9	存储过程【procedure】</h2> <h4 id="_1-7-1存储过程的优点"><a href="#_1-7-1存储过程的优点" class="header-anchor">#</a> 1.7.1	存储过程的优点</h4> <ol><li>存储过程可以减少网络流量</li> <li>允许模块化设计</li> <li>支持事务</li></ol> <h4 id="_1-7-2创建存储过程"><a href="#_1-7-2创建存储过程" class="header-anchor">#</a> 1.7.2	创建存储过程</h4> <p>语法：</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">procedure</span> 存储过程名<span class="token punctuation">(</span>参数<span class="token punctuation">)</span>
<span class="token keyword">begin</span>
	<span class="token comment">//sql语句</span>
<span class="token keyword">end</span><span class="token punctuation">;</span>

脚下留心：由于过程中有很多<span class="token keyword">SQL</span>语句，每个语句的结束都要用（；）结束。默认情况下，分号既表示语句结束，又表示向服务器发送<span class="token keyword">SQL</span>语句。我们希望分号仅表示语句的结束，不要将<span class="token keyword">SQL</span>语句发送到服务器执行，通过<span class="token keyword">delimiter</span>来更改结束符。
</code></pre></div><p>例题</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">delimiter</span> <span class="token comment">//</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">create</span> <span class="token keyword">procedure</span> <span class="token keyword">proc</span><span class="token punctuation">(</span><span class="token punctuation">)</span>     <span class="token comment">-- 创建存储过程</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">begin</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stuinfo<span class="token punctuation">;</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">end</span> <span class="token comment">//</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><h4 id="_1-7-3调用存储过程"><a href="#_1-7-3调用存储过程" class="header-anchor">#</a> 1.7.3	调用存储过程</h4> <p>语法：</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">call</span> 存储过程名<span class="token punctuation">(</span><span class="token punctuation">)</span>
</code></pre></div><p>例题：</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">call</span> <span class="token keyword">proc</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token comment">//     -- 调用存储过程</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> stuNo  <span class="token operator">|</span> stuName  <span class="token operator">|</span> stuSex <span class="token operator">|</span> stuAge <span class="token operator">|</span> stuSeat <span class="token operator">|</span> stuAddress <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> s25301 <span class="token operator">|</span> 张秋丽         <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">18</span> <span class="token operator">|</span>       <span class="token number">1</span> <span class="token operator">|</span> 北京           <span class="token operator">|</span>
<span class="token operator">|</span> s25302 <span class="token operator">|</span> 李文才        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">31</span> <span class="token operator">|</span>       <span class="token number">3</span> <span class="token operator">|</span> 上海          <span class="token operator">|</span>
<span class="token operator">|</span> s25303 <span class="token operator">|</span> 李斯文        <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">22</span> <span class="token operator">|</span>       <span class="token number">2</span> <span class="token operator">|</span> 北京           <span class="token operator">|</span>
<span class="token operator">|</span> s25304 <span class="token operator">|</span> 欧阳俊雄        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">28</span> <span class="token operator">|</span>       <span class="token number">4</span> <span class="token operator">|</span> 天津           <span class="token operator">|</span>
<span class="token operator">|</span> s25305 <span class="token operator">|</span> 诸葛丽丽         <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">23</span> <span class="token operator">|</span>       <span class="token number">7</span> <span class="token operator">|</span> 河南           <span class="token operator">|</span>
<span class="token operator">|</span> s25318 <span class="token operator">|</span> 争青小子        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">26</span> <span class="token operator">|</span>       <span class="token number">6</span> <span class="token operator">|</span> 天津           <span class="token operator">|</span>
<span class="token operator">|</span> s25319 <span class="token operator">|</span> 梅超风        <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">23</span> <span class="token operator">|</span>       <span class="token number">5</span> <span class="token operator">|</span> 河北          <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
<span class="token number">7</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><h4 id="_1-7-4删除存储过程"><a href="#_1-7-4删除存储过程" class="header-anchor">#</a> 1.7.4	删除存储过程</h4> <p>语法</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">drop</span> <span class="token keyword">procedure</span> <span class="token punctuation">[</span><span class="token keyword">if</span> <span class="token keyword">exists</span><span class="token punctuation">]</span> 存储过程名
</code></pre></div><p>例题：</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">drop</span> <span class="token keyword">procedure</span> <span class="token keyword">proc</span> <span class="token comment">//    -- 删除存储过程</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><h4 id="_1-7-5-查看存储过程的信息"><a href="#_1-7-5-查看存储过程的信息" class="header-anchor">#</a> 1.7.5  查看存储过程的信息</h4> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">show</span> <span class="token keyword">create</span> <span class="token keyword">procedure</span> 存储过程名\G
</code></pre></div><p>例题</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">show</span> <span class="token keyword">create</span> <span class="token keyword">procedure</span> <span class="token keyword">proc</span> \G
<span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span> <span class="token number">1.</span> <span class="token keyword">row</span> <span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span>
           <span class="token keyword">Procedure</span>: <span class="token keyword">proc</span>
            sql_mode: STRICT_TRANS_TABLES<span class="token punctuation">,</span>NO_AUTO_CREATE_USER<span class="token punctuation">,</span>NO_ENGINE_SUBSTITUTION
    <span class="token keyword">Create</span> <span class="token keyword">Procedure</span>: <span class="token keyword">CREATE</span> <span class="token keyword">DEFINER</span><span class="token operator">=</span><span class="token punctuation">`</span>root<span class="token punctuation">`</span><span class="token variable">@`localhost`</span> <span class="token keyword">PROCEDURE</span> <span class="token punctuation">`</span><span class="token keyword">proc</span><span class="token punctuation">`</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">begin</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stuinfo<span class="token punctuation">;</span>
<span class="token keyword">end</span>
character_set_client: gbk
collation_connection: gbk_chinese_ci
  <span class="token keyword">Database</span> Collation: utf8_general_ci
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><h4 id="_1-7-6-显示所有的存储过程"><a href="#_1-7-6-显示所有的存储过程" class="header-anchor">#</a> 1.7.6 显示所有的存储过程</h4> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">show</span> <span class="token keyword">procedure</span> <span class="token keyword">status</span> \G
</code></pre></div><h4 id="_1-7-7存储过程的参数"><a href="#_1-7-7存储过程的参数" class="header-anchor">#</a> 1.7.7	存储过程的参数</h4> <p>存储过程的参数分为：输入参数（in）【默认】，输出参数（out），输入输出参数（inout）</p> <p>存储过程不能使用return返回值，要返回值只能通过“输出参数”来向外传递值。</p> <p>例题一：传递学号，获取对应的信息</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">create</span> <span class="token keyword">procedure</span> <span class="token keyword">proc</span><span class="token punctuation">(</span><span class="token operator">in</span> param <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">)</span>   <span class="token comment">-- 输入参数</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stuinfo <span class="token keyword">where</span> stuno<span class="token operator">=</span>param <span class="token comment">//</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">call</span> <span class="token keyword">proc</span><span class="token punctuation">(</span><span class="token string">'s25301'</span><span class="token punctuation">)</span> <span class="token comment">//</span>
<span class="token operator">+</span><span class="token comment">--------+---------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> stuNo  <span class="token operator">|</span> stuName <span class="token operator">|</span> stuSex <span class="token operator">|</span> stuAge <span class="token operator">|</span> stuSeat <span class="token operator">|</span> stuAddress <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+---------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> s25301 <span class="token operator">|</span> 张秋丽        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">18</span> <span class="token operator">|</span>       <span class="token number">1</span> <span class="token operator">|</span> 北京           <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+---------+--------+--------+---------+------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><p>例题二：查找同桌</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">create</span> <span class="token keyword">procedure</span> <span class="token keyword">proc</span><span class="token punctuation">(</span>name <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">begin</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">declare</span> seat <span class="token keyword">tinyint</span><span class="token punctuation">;</span>   <span class="token comment">-- 声明局部变量</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">select</span> stuseat <span class="token keyword">into</span> seat <span class="token keyword">from</span> stuinfo <span class="token keyword">where</span> stuname<span class="token operator">=</span>name<span class="token punctuation">;</span>  <span class="token comment">-- 将座位号保存到变量中</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stuinfo <span class="token keyword">where</span> stuseat<span class="token operator">=</span>seat<span class="token operator">+</span><span class="token number">1</span> <span class="token operator">or</span> stuseat<span class="token operator">=</span>seat<span class="token operator">-</span><span class="token number">1</span><span class="token punctuation">;</span>  <span class="token comment">-- 查找同桌</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">end</span> <span class="token comment">//</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">call</span> <span class="token keyword">proc</span><span class="token punctuation">(</span><span class="token string">'李文才'</span><span class="token punctuation">)</span> <span class="token comment">//</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> stuNo  <span class="token operator">|</span> stuName  <span class="token operator">|</span> stuSex <span class="token operator">|</span> stuAge <span class="token operator">|</span> stuSeat <span class="token operator">|</span> stuAddress <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
<span class="token operator">|</span> s25303 <span class="token operator">|</span> 李斯文        <span class="token operator">|</span> 女      <span class="token operator">|</span>     <span class="token number">22</span> <span class="token operator">|</span>       <span class="token number">2</span> <span class="token operator">|</span> 北京           <span class="token operator">|</span>
<span class="token operator">|</span> s25304 <span class="token operator">|</span> 欧阳俊雄        <span class="token operator">|</span> 男       <span class="token operator">|</span>     <span class="token number">28</span> <span class="token operator">|</span>       <span class="token number">4</span> <span class="token operator">|</span> 天津           <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+----------+--------+--------+---------+------------+</span>
<span class="token number">2</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><p>强调</p> <div class="language- extra-class"><pre class="language-text"><code>1、通过declare关键字声明局部变量；全局变量@开头就可以了
2、给变量赋值有两种方法
	方法一：set 变量名=值
	方法二：select 字段 into 变量 from 表 where 条件
3、声明的变量不能与列名同名
</code></pre></div><p>例题三：输出参数</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">create</span> <span class="token keyword">procedure</span> <span class="token keyword">proc</span><span class="token punctuation">(</span>num <span class="token keyword">int</span><span class="token punctuation">,</span> <span class="token keyword">out</span> result <span class="token keyword">int</span><span class="token punctuation">)</span>  <span class="token comment">//out 表示输出参数</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">begin</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">set</span> result<span class="token operator">=</span>num<span class="token operator">*</span>num<span class="token punctuation">;</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">end</span> <span class="token comment">//</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">call</span> <span class="token keyword">proc</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span><span class="token variable">@result</span><span class="token punctuation">)</span> <span class="token comment">//</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token variable">@result</span> <span class="token comment">//</span>
<span class="token operator">+</span><span class="token comment">---------+</span>
<span class="token operator">|</span> <span class="token variable">@result</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">---------+</span>
<span class="token operator">|</span>     <span class="token number">100</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">---------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><p>例题四：输入输出参数</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">create</span> <span class="token keyword">procedure</span> <span class="token keyword">proc</span><span class="token punctuation">(</span><span class="token keyword">inout</span> num <span class="token keyword">int</span><span class="token punctuation">)</span>  <span class="token comment">#  inout 表示是输入输出参数</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">begin</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">set</span> num<span class="token operator">=</span>num<span class="token operator">*</span>num<span class="token punctuation">;</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">end</span> <span class="token comment">//</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">set</span> <span class="token variable">@num</span><span class="token operator">=</span><span class="token number">10</span><span class="token punctuation">;</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">call</span> <span class="token keyword">proc</span><span class="token punctuation">(</span><span class="token variable">@num</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token variable">@num</span> <span class="token comment">//</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

<span class="token operator">+</span><span class="token comment">------+</span>
<span class="token operator">|</span> <span class="token variable">@num</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+</span>
<span class="token operator">|</span>  <span class="token number">100</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre></div><h2 id="_1-10-go连接mysql"><a href="#_1-10-go连接mysql" class="header-anchor">#</a> 1.10 GO连接MySQL</h2> <ol><li>因为Go语言没有提供任何官方数据库驱动，所以需要安装第三方函数库。</li> <li>由于在github上安装，所以需要安装git软件，安装过程一直点击下一步即可。安装完成后需要配置环境变量</li></ol> <h4 id="_1-10-1-安装git"><a href="#_1-10-1-安装git" class="header-anchor">#</a> 1.10.1  安装git</h4> <p>git软件</p> <p><img src="http://static.zhequtao.com/study/mysql/day4/1537011074242.png" alt="1537011074242"></p> <p>安装完毕后，配置git的环境变量，这样可以使用<code>get</code>的指令</p> <p><img src="http://static.zhequtao.com/study/mysql/day4/1537094210154.png" alt="1537094210154"></p> <h4 id="_1-10-2-安装数据库驱动"><a href="#_1-10-2-安装数据库驱动" class="header-anchor">#</a> 1.10.2  安装数据库驱动</h4> <p>设置GOPATH的环境变量，这样驱动才会下载到项目根目录中。</p> <p><img src="http://static.zhequtao.com/study/mysql/day4/1537094342573.png" alt="1537094342573">****</p> <p>在cmd命令窗口中输入如下命令安装驱动</p> <div class="language-go extra-class"><pre class="language-go"><code><span class="token keyword">go</span> get github<span class="token punctuation">.</span>com<span class="token operator">/</span><span class="token keyword">go</span><span class="token operator">-</span>sql<span class="token operator">-</span>driver<span class="token operator">/</span>mysql

这一命令会从代码中获取驱动的具体代码，并将这些代码放置到包库中，当需要用到驱动的时候，编译器会把驱动代码与用户编写的代码一同编译。
</code></pre></div><p>安装完毕后，会在GOPATH下看到下载的驱动</p> <p><img src="http://static.zhequtao.com/study/mysql/day4/1537011722807.png" alt="1537011722807"></p> <h4 id="_1-10-3-连接数据库"><a href="#_1-10-3-连接数据库" class="header-anchor">#</a> 1.10.3  连接数据库</h4> <p>1、在src目录下创建demo.go文件，导入数据库驱动</p> <div class="language-go extra-class"><pre class="language-go"><code><span class="token keyword">import</span> <span class="token punctuation">(</span>
	<span class="token string">&quot;database/sql&quot;</span>
	<span class="token boolean">_</span> <span class="token string">&quot;github.com/go-sql-driver/mysql&quot;</span>
<span class="token punctuation">)</span>

注意：程序在操作数据库的时候只需要用到database<span class="token operator">/</span>sql，而不需要直接使用数据库驱动，所以程序在导入数据库驱动的时候将这个包的名字设置成下划线。
</code></pre></div><p>2、通过sql.open()连接数据库</p> <div class="language- extra-class"><pre class="language-text"><code>sql.open(驱动名,数据源dsn)(*DB,err)

数据源语法：&quot;用户名:密码@[连接方式](主机名:端口号)/数据库名&quot;

注意：open()在执行时不会真正的与数据库进行连接，只是设置连接数据库需要的参数
ping()方法才是连接数据库
</code></pre></div><p>3、执行SQL语句</p> <p>​	创建测试表</p> <div class="language-sql extra-class"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">create</span> <span class="token keyword">table</span> stu<span class="token punctuation">(</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> id <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> name <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span>
    <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token punctuation">)</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.02</span> sec<span class="token punctuation">)</span>
</code></pre></div><p>操作数据</p> <div class="language-sql extra-class"><pre class="language-sql"><code>package main

<span class="token keyword">import</span> <span class="token punctuation">(</span>
	<span class="token string">&quot;database/sql&quot;</span>
	_ <span class="token string">&quot;github.com/go-sql-driver/mysql&quot;</span>
	<span class="token string">&quot;fmt&quot;</span>
<span class="token punctuation">)</span>

func main<span class="token punctuation">(</span><span class="token punctuation">)</span>{
	<span class="token comment">//&quot;用户名:密码@[连接方式](主机名:端口号)/数据库名&quot;</span>
	db<span class="token punctuation">,</span>_:<span class="token operator">=</span><span class="token keyword">sql</span><span class="token punctuation">.</span><span class="token keyword">Open</span><span class="token punctuation">(</span><span class="token string">&quot;mysql&quot;</span><span class="token punctuation">,</span><span class="token string">&quot;root:root@(127.0.0.1:3306)/itcast&quot;</span><span class="token punctuation">)</span> <span class="token comment">// 设置连接数据库的参数</span>
	defer db<span class="token punctuation">.</span><span class="token keyword">Close</span><span class="token punctuation">(</span><span class="token punctuation">)</span>	<span class="token comment">//关闭数据库</span>
	err:<span class="token operator">=</span>db<span class="token punctuation">.</span>Ping<span class="token punctuation">(</span><span class="token punctuation">)</span>		<span class="token comment">//连接数据库</span>
	<span class="token keyword">if</span> err<span class="token operator">!=</span>nil{
		fmt<span class="token punctuation">.</span>Println<span class="token punctuation">(</span><span class="token string">&quot;数据库连接失败&quot;</span><span class="token punctuation">)</span>
		<span class="token keyword">return</span>
	}

	<span class="token comment">//操作一：执行数据操作语句</span>
	<span class="token comment">/*
	sql:=&quot;insert into stu values (2,'berry')&quot;
	result,_:=db.Exec(sql)		//执行SQL语句
	n,_:=result.RowsAffected();	//获取受影响的记录数
	fmt.Println(&quot;受影响的记录数是&quot;,n)
	*/</span>

	<span class="token comment">//操作二：执行预处理</span>
	<span class="token comment">/*
	stu:=[2][2] string{{&quot;3&quot;,&quot;ketty&quot;},{&quot;4&quot;,&quot;rose&quot;}}
	stmt,_:=db.Prepare(&quot;insert into stu values (?,?)&quot;)		//获取预处理语句对象
	for _,s:=range stu{
		stmt.Exec(s[0],s[1])			//调用预处理语句
	}
	*/</span>

	<span class="token comment">//操作三：单行查询</span>
	<span class="token comment">/*
	var id,name string
	rows:=db.QueryRow(&quot;select * from stu where id=4&quot;)   //获取一行数据
	rows.Scan(&amp;id,&amp;name)		//将rows中的数据存到id,name中
	fmt.Println(id,&quot;--&quot;,name)
	*/</span>

	<span class="token comment">//操作四：多行查询</span>
	<span class="token keyword">rows</span><span class="token punctuation">,</span>_:<span class="token operator">=</span>db<span class="token punctuation">.</span>Query<span class="token punctuation">(</span><span class="token string">&quot;select * from stu&quot;</span><span class="token punctuation">)</span>		<span class="token comment">//获取所有数据</span>
	var id<span class="token punctuation">,</span>name string
	<span class="token keyword">for</span> <span class="token keyword">rows</span><span class="token punctuation">.</span><span class="token keyword">Next</span><span class="token punctuation">(</span><span class="token punctuation">)</span>{		<span class="token comment">//循环显示所有的数据</span>
		<span class="token keyword">rows</span><span class="token punctuation">.</span>Scan<span class="token punctuation">(</span><span class="token operator">&amp;</span>id<span class="token punctuation">,</span><span class="token operator">&amp;</span>name<span class="token punctuation">)</span>
		fmt<span class="token punctuation">.</span>Println<span class="token punctuation">(</span>id<span class="token punctuation">,</span><span class="token string">&quot;--&quot;</span><span class="token punctuation">,</span>name<span class="token punctuation">)</span>
	}
}
</code></pre></div><h2 id="_1-11-小练习"><a href="#_1-11-小练习" class="header-anchor">#</a> 1.11  小练习</h2> <p>1、	显示地区及每个地区参加考试的人数，并按人数降序排列。</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">select</span> stuaddress<span class="token punctuation">,</span><span class="token function">count</span><span class="token punctuation">(</span>writtenexam<span class="token punctuation">)</span> c <span class="token keyword">from</span> stuinfo <span class="token keyword">left</span> <span class="token keyword">join</span> stumarks <span class="token keyword">using</span><span class="token punctuation">(</span>stuno<span class="token punctuation">)</span> <span class="token keyword">group</span> <span class="token keyword">by</span> stuaddress <span class="token keyword">order</span> <span class="token keyword">by</span> c <span class="token keyword">desc</span><span class="token punctuation">;</span>
</code></pre></div><p>2、	显示有学生参加考试的地区</p> <p>方法一：过滤</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">select</span> stuaddress<span class="token punctuation">,</span><span class="token function">count</span><span class="token punctuation">(</span>writtenexam<span class="token punctuation">)</span> c <span class="token keyword">from</span> stuinfo <span class="token keyword">left</span> <span class="token keyword">join</span> stumarks <span class="token keyword">using</span><span class="token punctuation">(</span>stuno<span class="token punctuation">)</span> <span class="token keyword">group</span> <span class="token keyword">by</span> stuaddress <span class="token keyword">having</span> c<span class="token operator">&gt;</span><span class="token number">0</span><span class="token punctuation">;</span>
</code></pre></div><p>方法二：链接查询</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token keyword">distinct</span> stuaddress c <span class="token keyword">from</span> stuinfo <span class="token keyword">right</span> <span class="token keyword">join</span> stumarks <span class="token keyword">using</span><span class="token punctuation">(</span>stuno<span class="token punctuation">)</span> <span class="token punctuation">;</span>
</code></pre></div><p>3、	显示男生和女生的人数</p> <p>方法一：</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">select</span> stusex<span class="token punctuation">,</span><span class="token function">count</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">from</span> stuinfo <span class="token keyword">where</span> stusex<span class="token operator">=</span><span class="token string">'男'</span> <span class="token keyword">union</span> <span class="token keyword">select</span> stusex<span class="token punctuation">,</span><span class="token function">count</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">from</span> stuinfo <span class="token keyword">where</span> stusex<span class="token operator">=</span><span class="token string">'女'</span><span class="token punctuation">;</span>
</code></pre></div><p>方法二：</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token function">sum</span><span class="token punctuation">(</span>stusex<span class="token operator">=</span><span class="token string">'男'</span><span class="token punctuation">)</span> 男<span class="token punctuation">,</span><span class="token function">sum</span><span class="token punctuation">(</span>stusex<span class="token operator">=</span><span class="token string">'女'</span><span class="token punctuation">)</span> 女 <span class="token keyword">from</span> stuinfo<span class="token punctuation">;</span>
</code></pre></div><p>4、	显示每个地区的男生和女生和总人数</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">select</span> stuaddress<span class="token punctuation">,</span><span class="token function">count</span><span class="token punctuation">(</span>stusex<span class="token punctuation">)</span> 总人数<span class="token punctuation">,</span> <span class="token function">sum</span><span class="token punctuation">(</span>stusex<span class="token operator">=</span><span class="token string">'男'</span><span class="token punctuation">)</span> 男<span class="token punctuation">,</span><span class="token function">sum</span><span class="token punctuation">(</span>stusex<span class="token operator">=</span><span class="token string">'女'</span><span class="token punctuation">)</span> 女 <span class="token keyword">from</span> stuinfo <span class="token keyword">group</span> <span class="token keyword">by</span> stuaddress
</code></pre></div></div> <footer class="page-edit"><!----> <div class="last-updated"><span class="prefix">上次更新:</span> <span class="time">7/20/2020, 2:09:44 AM</span></div></footer> <div class="page-nav"><p class="inner"><span class="prev">
      ←
      <a href="/mysql/03.数据库设计与查询语句.html" class="prev">3、  数据库设计与查询语句</a></span> <span class="next"><a href="/mysql/MySQL高级知识-第1章.html">MySQL高级知识-第1章</a>
      →
    </span></p></div> </main></div><div class="global-ui"></div></div>
    <script src="/assets/js/app.cb35c8f6.js" defer></script><script src="/assets/js/2.063846a6.js" defer></script><script src="/assets/js/4.1ffb4609.js" defer></script><script src="/assets/js/590.1a06b2a0.js" defer></script>
  </body>
</html>
